Automatically delete rows

I

Ivor Williams

I have a spreadsheet that was created in and exported from QuickBooks.
Before manipulating data in the spreadsheet, I want to delete all rows that
contain anything in column C. What function or code could I use to do this?
Could it be done using a macro? I have to do this many times in a month, so
would like to automate the process as much as possible.
 
G

Gord Dibben

Ivor

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, "C").Value <> "" Then
Rows(RowNdx).Delete
End If
Next RowNdx
End Sub


Gord Dibben Excel MVP
 
R

Ron de Bruin

Hi Ivor

Warning :

If your data start in row 3 for example Gord's example will not loop
through the last two rows of your data.
If your data start in row 1 then there is no problem

See example 1 on this page for a solution
http://www.rondebruin.nl/delete.htm
 
G

Gord Dibben

Thanks for the correction Ron.

Not tested thoroughly. Assumed data start in row 1.


Gord
 
I

Ivor Williams

Thank you for your response, Gord and Ron.
I can see how the code will work, but there is one small problem. I'm very
familiar with Access and how an event will cause the code to run. How is
this accomplished in Excel?

Ivor
 
Top