Use If Statement to Delete Entire Column Based on Specific Text of a Cell

jewkes6000

New Member
Joined
Mar 25, 2020
Messages
46
Office Version
  1. 365
Platform
  1. Windows
I am trying to cycle through several columns and delete the columns which have the word "HIDE" in a specific cell. I'm able to cycle through the cells; however, whenever a cell contains the word "Hide", it skips it as if the statement is NOT true. The cell does contain a formula which generates the cell value, but I though the code should still recognize the value. Why is the if statement "skipping" to the next column when the cell actually says "Hide"? I've also included a google drive link below to see the actual file.

Any help is much appreciated.


VBA Code:
Sub Delete_Columns()

'This will cycle through columns and delete those which are marked as "hide"
    For i = 22 To 237
        Columns(i).Select
        
        If ActiveCell = "Hide" Then
        Columns(i).Delete
        Else
        ActiveCell.Offset(1, 0).Range("A1").Select
        End If

        If ActiveCell = "Hide" Then
        Columns(i).Delete
        Else
        End If
    
    Next i
      
End Sub


 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,470
Office Version
  1. 365
Platform
  1. Windows
Perhaps case is the issue.

Try changing these references:
VBA Code:
ActiveCell = "Hide"
to this:
VBA Code:
UCase(ActiveCell) = "HIDE"

By the way, which row are you checking? We can simplify, improve and speed up your code.
If the words always appear in row 1, then you could re-write your code:
VBA Code:
Sub Delete_Columns()

    Dim i As Long
    
    Application.ScreenUpdating = False

    For i = 237 To 22 Step -1
        If UCase(Cells(1, i)) = "HIDE" Then Columns(i).Delete
    Next i
    
    Application.ScreenUpdating = True
      
End Sub
 
Solution

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,470
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
Glad I was able to help.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,009
Messages
5,575,535
Members
412,673
Latest member
KD23
Top