How do I autodelete rows in a macro on a variable dataset?

[bimpnottin_noregard]

I'm trying to create a macro that would delete rows that do not contain
information. I've tried doing an autofilter to blanks and deleting those, but
this is from a variable dataset, so my row designations won't be the same
every time I run the filter.

It's a big hangup on running this report, because the way the .csv file is
set into excel is that it automatically includes a summary row for each
customer and I need to get rid of that to not have duplicate column totals.

 

[Gord Dibben]

If the rows are blank, select any column and F5>Special>Blanks>OK

Edit>Delete>Entire Row.

If not blank, what would differentiate the summary rows from the others?


Gord Dibben MS Excel MVP

 

[bimpnottin_noregard]

The rows are not completely blank, but the cell in the invoice column is
blank, that's my designator.

 

[Gord Dibben]

Select the invoice column and do the F5>Special.

You can record a macro at same time.

Or change the "A" to the column in question and run this.

Sub delete_rows()
Dim RowNdx As Long
Dim LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
For RowNdx = LastRow To 1 Step -1
If Cells(RowNdx, "A").Value = "" Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub


Gord

 

[bimpnottin_noregard]

When I do the F5 Special, I can choose blanks, but what would I need to do
after that.

I've been trying the macro, and everything looks right, but I don't know if
it's not working because I'm just trying to record it as a sub macro to test
it, or if I'm doing something else wrong. I changed the "A" to "invoice" to
match my column heading.

 

[Dave Peterson]

Change the value to the column header that you see at the top--not the headers
that you've typed in.

It can be A through IV (in xl2003 and below).

And Gord's post had these three steps:
select any column
and F5>Special>Blanks>OK
Edit>Delete>Entire Row.

 

[bimpnottin_noregard]

Thank you. Both of those now worked. Made me glad that I put what I had
done, so you could fix it. I know enough about macros to create them, but
not always fix them.