Macro to hide columns in a range that do not contain

[Celia9]

Hello,

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

Thanks!

[#2]

Sub HideCols()
Columns("I:BH").EntireColumn.Hidden = (Range("A11") = "X")
End Sub

[#3]

But this just looks to A11. It needs to look in each column at row 11.

[#4 · Accepted solution]

@Celia9 

Sub HideCols()
    Dim cell As Range
    For Each cell In Range("I11:BH11")
        cell.EntireColumn.Hidden = (cell.Value <> "X")
    Next cell
End Sub

[#5]

Thanks @hans
Could you also help me when I want to hide everything but containing X?
Something like <> "*X"

[#6]

@Celia9 

Change

(cell.Value <> "X")

 to

(Not cell.Value Like "*X*")

[#7]

Thank you!

[#8]

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

[#9]

@Celia9 

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.