Forum Discussion

Brass Contributor

Jul 26, 2022

Solved

Macro to hide columns in a range that do not contain

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...

Macros and VBA

HansVogelaar

Jul 26, 2022

Celia9

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

Celia9

Brass Contributor

Aug 02, 2022

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

HansVogelaar

MVP

Aug 02, 2022

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.