Excel macro to delete rows if a column contains certain text?

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

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