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

  • Thread starter Thread starter bimpnottin_noregard
  • Start date Start date
B

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.
 
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
 
The rows are not completely blank, but the cell in the invoice column is
blank, that's my designator.
 
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
 
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.
 
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.
 
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.
 
Back
Top