Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.

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.

1 Answer

Relevance
  • 1 decade ago
    Favorite 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

Still have questions? Get your answers by asking now.