Using Worksheet name in VBA

[redbug]

I have a little code to check a cell for text, and if the text is NOT there the code deletes the row. Here is the example that works; if the text "keep this" does not appear in the cell (in column D) the entire row is deleted:

Sub Del_Not_Match_Simple()
Dim Rng As Range
Columns(4).Insert
Set Rng = Range([E1], [E65536].End(xlUp)).Offset(, -1)
With Rng
.FormulaR1C1 = "=SEARCH(""keep this"",RC[1])"
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
On Error GoTo 0
.EntireColumn.Delete
End With
Set Rng = Nothing

End Sub

What I need is for the code to work using the sheet name as the test text. I can't make it work.

Here is an example of the code when I try to use a variable to use the sheet name, the locals window looks OK, but I get errors in all rows.

Sub Del_Not_Match_v1_SheetName()
Dim Rng As Range
Dim SName
SName = ActiveSheet.Name
Columns(4).Insert
Set Rng = Range([E1], [E65536].End(xlUp)).Offset(, -1)
With Rng
.FormulaR1C1 = "=SEARCH(""SName"",RC[1])"
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
On Error GoTo 0
.EntireColumn.Delete
End With
Set Rng = Nothing
Set SName = Nothing
End Sub

I can't figure out how to get the sheet name to work here. Can anyone help?

 

[shg]

Welcome to the board.

Try this:

Sub Del_Not_Match_v1_SheetName()
    Columns("D").Insert
 
    With Intersect(Columns("D"), ActiveSheet.UsedRange)
        .FormulaR1C1 = "=SEARCH(""" & ActiveSheet.Name & """,RC[1])"
        On Error Resume Next
        .SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
        .EntireColumn.Delete
    End With
End Sub

 

[redbug]

Truly helpful. Thank you so much.