CODEWITHSUNDEEP
vbaexcel
TonyP
TonyP

Reputation: 333

Using VBA if statements to hide/unhide columns with a button

I am trying to create a clickable toggle button that hides a year's worth of data if it is currently unhidden and unhides it if it is hidden. I know this syntax is incorrect but I am not sure how to make it work. Any help would be appreciated.

Sub Hide_2012()
Dim Yr2012 As Range
    Set Yr2012 = ThisWorkbook.Worksheets("Open Jobs Calculations").Range("AI:AT")

If Yr2012.Visible = False Then
     Yr2012.Visible = True
Else If Yr2012.Visible = True Then
     Yr2012.Visible = False
End If

Upvotes: 1

Views: 2566

Answers (2)

findwindow
findwindow

Reputation: 3153

This works for me. Apparently hidden only works on entire columns.

Dim Yr2012 As Range
Set Yr2012 = ThisWorkbook.WorkSheets("Open Jobs Calculations").Range("A1:B10") 'change range

If Yr2012.EntireColumn.Hidden = False Then
     Yr2012.EntireColumn.Hidden = True
ElseIf Yr2012.EntireColumn.Hidden = True Then
    Yr2012.EntireColumn.Hidden = False
End If

Edit per Scott's comment:

Yr2012.EntireColumn.Hidden = Not Yr2012.EntireColumn.Hidden

much more elegant.

Upvotes: 7

burrito
burrito

Reputation: 31

This modification of @findwindow's answer worked for me:

Set Yr2012 = ThisWorkbook.Worksheets("Open Jobs Calculations").Columns("AI:AT")  ' <- (ie, set range to the desired columns)

then you can still reference the "Yr2012" range, without needing ".EntireColumn":

If Yr2012.Hidden = False Then ...

Just another take on the same idea, to reduce typing :)

Upvotes: 0

Related Questions