Forum Discussion
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()
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!
Sub HideCols() Dim cell As Range For Each cell In Range("I11:BH11") cell.EntireColumn.Hidden = (cell.Value <> "X") Next cell End Sub
8 Replies
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.
- 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.
