excel macro to delete rows containing non values?
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.
More Questions From This User See All
Answers & Comments
Verified 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