How to delete rows that contain certain text in a cell using a formula

If it's pretty urgent, I'd recommend attaching a real example of your work. It's going to take me a couple of hours to recreate your picture from scratch.

Other than that you might have luck with a filter with copy and paste, a few hundred array formulas, or... VBA which could do it all in less than a second.

Here's on macro that can do it. First, copy the tab's headers to another tab.

In order to use VB:

• copy the code below
• go to Excel
• Hit Alt+F11
• Go to Insert -> Module
• Paste the code in the window that opens
• Close that window

Please Login or Register  to view this content.

Now go back to the first tab, hit Alt+F8 and run your macro. Every line without that in it will copy over.


The attached is ready to go.

I looked around and found a MUCH faster and simpler method.

Change the code between the asterisks as needed to whatever you want to delete. This instantly kills all rows you want out, and it does it in place without copying anywhere else.

VB code:

Please Login or Register  to view this content.

Broken apart:

Sub Filter()
With ActiveSheet
.AutoFilterMode = False
With Range("B2", Range("B" & Rows.Count).End(xlUp)) = from B2 to the last non-blank cell
.AutoFilter 1, "*OTHER (COMBINED) COUNTIES*" =look for this
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete =delete each row that has it
End With
.AutoFilterMode = False
End With
End Sub[/CODE]

Hurray! Happiness for everyone.