excel macro to delete rows containing non values?

Question

I need a macro that deletes rows with a non value in column C and D.  If there is a value in either column I want the record to remain. Only if both columns are non values does the row need to be deleted.  I would prefer to not have to specify the range.  Sometimes I may have 100 rows and othe times 200,000 rows.  I want the macro to work regardless how many records are on the worksheet.

Cozmosis · Accepted Answer

I'm assuming by "non value" you mean blank or empty. 


Sub Delete_Empty_CD_Rows()
Dim r As Long
For r = Cells.Find("*", [A1], SearchDirection:=xlPrevious).Row To 1 Step -1
If IsEmpty(Cells(r, "C")) And IsEmpty(Cells(r, "D")) Then Rows(r).Delete
Next r
End Sub


If you meant Non-numeric (C or D could be text or a date) and you want to delete the row if both are non-numeric, then change...
If IsEmpty(Cells(r, "C")) And IsEmpty(Cells(r, "D")) Then Rows(r).Delete

 to this...
If Not IsNumeric(Cells(r, "C")) And Not IsNumeric(Cells(r, "D")) Then Rows(r).Delete

Trending News

excel macro to delete rows containing non values?

1 Answer