Delete entire column based on cell value

[rabbits]

I have a large workbook with multiple columns. In row 9 I have labels that are either 'REMOVE', 'CHECK' or 'INSTALL'. I'm trying to write a macro that deletes all columns with 'remove'. The number of remove columns varies every time.

I've adapted this macro from something I found online, it sort-of works but seems to only delete every other remove column. Could someone tell me where I'm going wrong?

Sub Delete_Column()
Dim cell As Range
For Each cell In Range("9:9")
    If cell.Value = "REMOVE" Then
        cell.EntireColumn.Delete
    End If
Next cell
End Sub

 

[Joe4]

Welcome to the Board!

The issue is that when deleting columns, the other columns shift over to the left, into the range you already checked.
So you need to loop through the column backwards, like this:

Sub Delete_Column()

    Dim lc As Long
    Dim c As Long
    
'   Find last column used in row 9
    lc = Cells(9, Columns.Count).End(xlToLeft).Column
    
'   Loop through all columns backwards
    For c = lc To 1 Step -1
        If Cells(9, c).Value = "REMOVE" Then Cells(9, c).EntireColumn.Delete
    Next c
    
End Sub

 

[rabbits]

Thanks for this - worked like a dream.

Just as a follow-up, how would I adapt the code to delete anything that's NOT a certain word, or that contains a certain word?

 

[Joe4]

For NOT certain word, change the "=" to "<>", i.e.

Cells(9, c).Value = "REMOVE"

becomes

Cells(9, c).Value <> "REMOVE"

For contains a certain word, use the INSTR function, i.e.

If InStr(Cells(9, c).Value, "Remove") > 0 Then