How to delete entire column if row x does not contain certain words

[davey4444]

Hi there,

I've seen various pieces of code which are aimed at deleting rows if certain text does or does not appear, e.g -

Sub RemoveRows()Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    If Not Range("A" & i).Value Like "Subtotal*" Then Rows(i).Delete
Next i
End Sub

However I can't seem to manipulate this to work for columns. All I want to do is check row 3 all the way across my sheet and delete any columns which don't contain the words "dog" or "cat".

Hopefully this is a simple one to work out for you as I just cannot get it to work.

Thanks.

 

[mumps]

Try:

Sub DeleteCols()
    Application.ScreenUpdating = False
    Dim lColumn As Long
    lColumn = ActiveSheet.Cells(3, Columns.Count).End(xlToLeft).Column
    Dim x As Long
    For x = lColumn To 1 Step -1
        If Not Cells(3, x) Like "dog*" And Not Cells(3, x) Like "cat*" Then
            Columns(x).EntireColumn.Delete
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

Try this:

I assume the cell value is "Cat" or "Dog" and not like:
"The "Dog" was here".

Sub Test()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastcolumn = Cells(3, Columns.Count).End(xlToLeft).Column
For i = Lastcolumn To 1 Step -1
If Cells(3, i).Value <> "Dog" And Cells(3, i) <> "Cat" Then Columns(i).Delete
Next
Application.ScreenUpdating = True
End Sub