Forum Discussion
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...
Sub HideCols() Dim cell As Range For Each cell In Range("I11:BH11") cell.EntireColumn.Hidden = (cell.Value <> "X") Next cell End Sub
Sub HideCols()
Dim cell As Range
For Each cell In Range("I11:BH11")
cell.EntireColumn.Hidden = (cell.Value <> "X")
Next cell
End Sub- 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:BH11Using 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 RangeBut 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 SubChange Sheet1 to the name of the relevant worksheet.
- Thanks hans
Could you also help me when I want to hide everything but containing X?
Something like <> "*X"- Thank you!
