VBA to delete rows where text is not present

[Regular7]

Hi,

I am pulling my hair out over this one. I basically want a macro to delete all the rows where 'Fox' appears in a cell (i.e IF Fox exists in the text in Column B, or Column C, or Column D, then delete the entire row. The number of rows will be variable. I can't work out how to use ifs in vba without getting a 'if without block if' error (this is a bit beyond my ability).

The example file is attached. Many thanks in advance.

 

[davesexcel]

Your title reads "VBA to delete rows where text is not present " but your question wants to delete rows where the text is present.

this will delete the rows that the text is present.

Sub SelectA1()
    Dim FrstRng As Range
    Dim UnionRng As Range
    Dim c As Range
    Set FrstRng = Range("B1:D" & Cells(Rows.Count, "D").End(xlUp).Row)

    For Each c In FrstRng.Cells
        If InStr(1, c.Text, "fox", vbTextCompare) > 0 Then
            If Not UnionRng Is Nothing Then
                Set UnionRng = Union(UnionRng, c)    'adds to the range
            Else
                Set UnionRng = c
            End If
        End If
    Next c

    UnionRng.EntireRow.Delete
End Sub

 

[Rick Rothstein]

Give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub DeleteRowsContainFox()
  With ActiveSheet.UsedRange
    .Replace "*Fox*", "#N/A", xlWhole, , False, , False, False
    On Error GoTo NoFoxes
    Intersect(.EntireColumn, .SpecialCells(xlConstants, xlErrors).EntireRow).Delete
  End With
NoFoxes:
End Sub[/td]
[/tr]
[/table]