CODEWITHSUNDEEP
vbaexcel
kicpereniek
kicpereniek

Reputation: 47

Macro to Hide/Unhide Columns in Excel

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 BG (formerly 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

Upvotes: 2

Views: 7394

Answers (1)

Tom
Tom

Reputation: 9898

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

Upvotes: 5

Related Questions