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

excel macro to delete rows containing non values?

1 Answer