Macro to Hide/Unhide Columns in Excel

Question

I created a simple macro to hide/unhide columns in Excel (attached below). It works fine, however, when adding a column within the range of columns in the macro, the last column supposed to be hidden remains unhidden. To make myself clear: The range of columns in macro is AM:BF. When I need to add a column within this range, the column BF stays unhidden. Could you help me how to improve the code so that the initial range of columns would stay hidden as well as the added one?

With Columns("AM:BF")
    If .EntireColumn.Hidden = True Then
        .EntireColumn.Hidden = False
    Else
        .EntireColumn.Hidden = True
    End If
End With

Tom Tom · Accepted Answer · 2017-02-01T14:11:43

You need to have a placer for the column. You could used a named range along the top row of columns AM:BF (Which will then change if you add a column in the middle of it). Your code could then look like

With ThisWorkbook.Sheets("MySheet").Range("NamedRange").EntireColumn
    .Hidden = Not .Hidden   
End With

Macro to Hide/Unhide Columns in Excel

1 Answers