VBA Code Deleting a Column instead of Rows...

[MattGreer]

I have a structured table that looks a bit like this (the dots are so everything lines up here):


[tt]
Column1..|Column2 ..|Column3...|Column4...|
Info.....|Info......|Info......|Info......|
Blank....|Info......|Info......|Info......|
Blank....|Info......|Info......|Info......|
Blank....|Info......|Info......|Info......|
Blank....|Info......|Info......|Info......|
Blank....|Info......|Info......|Info......|
Blank....|Blank.....|Blank.....|Blank.....|
Blank....|Blank.....|Blank.....|Blank.....|
Blank....|Blank.....|Blank.....|Blank.....|
Blank....|Blank.....|Blank.....|Blank.....|
etc.
[/tt]

I'm trying to remove all the completely blank rows, so I tried this code that I found:

With tbl
    .ListColumns("Column2").DataBodyRange.SpecialCells(xlCellTypeBlanks).Rows.Delete
End With

but what happens is that Column2 gets deleted. I'm confused as to why that's happening? I don't see how that code is deleting a *column*.


Thanks!!


Matt

 

[Andrzejek]

>the dots are so everything lines up here
You should know by now - use [ignore][Pre] [/Pre][/ignore] tags

[pre]
Column1 Column2 Column3 Column4
Info Info Info Info
Blank Info Info Info
Blank Info Info Info
Blank Info Info Info
Blank Info Info Info
Blank Info Info Info
Blank Blank Blank Blank
Blank Blank Blank Blank
Blank Blank Blank Blank
Blank Blank Blank Blank
etc.[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[MattGreer]

Good to know.

Why is my column getting deleted? <cry>

Thanks!!


Matt

 

[SkipVought]

Matt, are these empty rows interspersed in your table data or are they ALL and ALWAYS at the bottom of your table as you have displayed?

The other question is, how did it get that way?

I can remember, way back in the 1990s, making tables (way before there were Structured Tables) where I had FORMULAE in rows in advance of expected new data, often a CUT N PASTE situation. With Structured Tables and previously List Tables (if my memory serves me) it is not necessary to advance load formulae as they propagate into new rows.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[MattGreer]

Heya Skip! Hope you're doing well.

In this case, Excel is acting as a go-between from one software package to another, and unfortunately the two software packages don't speak the same language.

The table above is post-processed from the first program. The first column has a 'Label' in the first row, and subsequent to that is the data that pertains to that label. Columns 2, 3, and 4 will always have the same number of data points, so once the data stops on Column 2/3/4, that's it. The rest of the rows are blank.

The reason the table is this way is because what's in that first column is a label that represents the name of the set of data that follows. So the raw cut and paste from the source program looks like this:

[pre]
Column1 Column2 Column3 Column4
Label 1 Data Data Data
blank Data Data Data
blank Data Data Data
Label 2 Data Data Data
blank Data Data Data
blank Data Data Data
Label 3 Data Data Data
blank Data Data Data
blank Data Data Data
[/pre]
and so on.

There's between 2 and 75 rows of data for each Label. So the way the flow goes right now is the macro takes the bulk pasted data from the first worksheet, copies it to a new worksheet, creates a table around the data, looks for where the next data label shows up, cuts all the rows from that point to the end of the table, pastes the data to the right and creates a new table around that. The tables are named using the Label string so it makes it easy to go grab the data for input into the second program. Anyway, what I'd like to do is delete all the blank rows that are left over in the first table after I've moved the data over. At the end of the process there's about 75 tables of different sizes.

I don't recall, but I think there's a reason why I want to get rid of all the blank rows. I've had to move to other projects since I first started working on this so now that I've returned I'm trying to clean up the code a bit.


Hope this makes sense!

Thanks!!


Matt

 

[SkipVought]

Well not entirely.

The tables are named using the Label string

That makes sense. You end up with 2 to 75 rows in each of these tables.

But then I'm not seeing HOW empty rows end up at the end of...
only ONE table
ALL tables
???


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[SkipVought]

What have I been thinking! Got this epiphany whilst viewing The Great British Bake Off. Don't ask the association.

1) Add a helper column to your table for a formula.
2) Formula: =AND(ISBLANK([@Column1]),ISBLANK([@Column2]),ISBLANK([@Column3]),ISBLANK([@Column4]))

[pre]
Column1 Column2 Column3 Column4 Helper
Info Info Info Info FALSE
Info Info Info FALSE
Info Info Info FALSE
TRUE
TRUE
TRUE
TRUE
TRUE
TRUE
[/pre]

Since this is a Structured Table, you enter the formula in the first dataarea row and it propagates down thru all rows.

3) Then you FILTER the Helper column on TRUE, SELECT those rows, and DELETE.

4) Finally DELETE the Helper column.

You could quite easily record a macro of the entire process.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!