Macro to hide columns in a range that do not contain




I found a macro that almost fits my needs, can someone help me out to complete?


I want to hide columns between range I:BH based on what is not in the cell in row 11



Sub HideCols()
Dim cell As Range
For Each cell In ActiveWorkbook.ActiveSheet.Rows("8").Cells
If cell.Value = "X" Then
cell.EntireColumn.Hidden = True
End If
Next cell
End Sub



Where do I set my range I:BH?


And I suppose I just change If cell.Value = "X" Then to If cell.Value <> "X" Then



8 Replies
Sub HideCols()
Columns("I:BH").EntireColumn.Hidden = (Range("A11") = "X")
End Sub
But this just looks to A11. It needs to look in each column at row 11.
best response confirmed by Celia9 (Contributor)


Sub HideCols()
    Dim cell As Range
    For Each cell In Range("I11:BH11")
        cell.EntireColumn.Hidden = (cell.Value <> "X")
    Next cell
End Sub
Thanks @hans
Could you also help me when I want to hide everything but containing X?
Something like <> "*X"



(cell.Value <> "X")


(Not cell.Value Like "*X*")
Hi Hans,

Could you also help me to make the variable for the Range?

I have to start the module with Option Explicit then right?
I would like to have one variable for the entire module for I11:BH11


Using Option Explicit in all your code modules is always a good idea - see The importance of 'Option Explicit' 


If you want to define a variable that will be available in all modules in a workbook, you have to declare the variable at the top of a module (but below Option Explicit) as Public:

Public MyRange As Range

But you also have to assign a value to this variable. You might do this in the ThisWorkbook module, in the Workbook_Open event procedure. This will automatically be executed each time the workbook is opened:

Private Sub Workbook_Open()
    Set MyRange = Worksheets("Sheet1").Range("I11:BH11")
End Sub

Change Sheet1 to the name of the relevant worksheet.