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 if a column contains certain text?

I want to delete all rows in a worksheet if the text in column D begins with "CURRENCY". It will be included in the leftmost 8 characters of that cell if it exists.

This only need to be for the active worksheet.

2 Answers

Relevance
  • 1 decade ago
    Favorite Answer

    Sub Delete_CURRENCY_Rows()

    Dim r As Long

    For r = Range("D" & Rows.Count).End(xlUp).Row To 1 Step -1

    If UCase(Left(Cells(r, "D").Text, 8)) = "CURRENCY" Then Rows(r).Delete

    Next r

    End Sub

  • ?
    Lv 4
    5 years ago

    Siti V's answer is pretty good. But David W did include one thing in his code that I would make sure to include, althought his code is faulty. David W's code is faulty for two reasons. First, you need to delete the rows from the last row and work your way to the first row like Siti did. You'll notice that she used a Step -1, which makes her macro work from the bottom to the top like a macro should when deleting rows. This is because the rows shift up when you delete a row and otherwise you will end up skipping a row. David W included a good feature in his macro, but he didn't finish it making it faulty in another way. It's normally a good practice to turn the screen updating temporarily off while you are having a macro change cell values. This increases the speed of the macro because each time a cell value is changed the screen has to be updated if the screen updating property is set to true. So you want to set the screen updating to false at the beginning of the macro. However, at the end of the macro you need to turn the screen updating back on so that you can see the results of the macro. If the screen updating isn't turned back on, then you don't see the results. I tried Siti Vi's macro also and I got it to work and I also got it to fail. Hers works if you have data on row 1. If you don't then the macro fails and deletes all but one row. I took her macro and modified it a little. I tested the macro and it works fine even if row 1 is empty. The macro should also run fast since the screen updating is turned off while the macro is running. Sub Delete_BlueRedRows() Dim MyRange As Range, r As Long Dim LastRow As Long, FirstRow As Long Set MyRange = ActiveSheet.UsedRange 'Finds the row number of the row the data starts on FirstRow = MyRange.Row 'Finds the row number of the last row of data LastRow = MyRange.Row + MyRange.Rows.Count - 1 'Turn the screen updating off to make the macro run faster Application.ScreenUpdating = False For r = LastRow To FirstRow Step -1 If UCase(Trim(Range("J" & r))) = "RED" Or _ UCase(Trim(Range("J" & r))) = "BLUE" Then Range("J" & r).EntireRow.Delete End If Next r 'Turn the screen updating back on so the changes are shown Application.ScreenUpdating = True End Sub

Still have questions? Get your answers by asking now.