Hide Many Non-Contiguous Columns using a Macro based on a dropdown Box

Question

Hi,

So I've got a dropdown box with a macro that runs Hide commands based on the value of the dropdown cell.  Here's a look at the code

___________________

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Select Case Range("a7")

Case "Simple"

[tmi.rs].EntireRow.Hidden = True

[tmi.cs].Entirecolumns.Hidden = True

Case "Advanced"

[tmi.rs].EntireRow.Hidden = False

[tmi.cs].Entirecolumns.Hidden = False

End Select

End Sub

____________________

I am able to successfully operate the Macro, however, for the named references i'm using i am unable to get a selection for all of the columns i am trying to hide.  They are non contiguous.  Does anyone know of an Offset formula that can select whole columns?

Thanks,

Johnny

Answer 1

It is not exactly what you are asking, but if you use a range name you can do this

Define HideColumns as =Sheet1!A1,Sheet1!C1

Sub ShowHide ()

Range("HideColumns").Select

Selection.EntireColumn.Hidden = True

'Hides columns A and C

Range("HideColumns").Select

Selection.EntireColumn.Hidden = False

Unhides columns A and C

End Sub