VBA. unhide columns based on value in one specific cell.

**rain4u** · 03-15-2012, 11:48 PM

Hi
Right. Where to start
On a sheet called "column hide settings" I have some names in range C2:C6 (i.e. All, Section 1, Section 2, Section 3, Section 4) and some values in D2:D6 (i.e H:M, Q:U, Y:Z, AD:AP H:M Q:U Y:Z AD:AP) as below:

All	H:M, Q:U, Y:Z, AD:AP
Section 1	H:M
Section 2	Q:U
Section 3	Y:Z
Section 4	AD:AP

Now on another sheet (within same workbook) I have a dropdown box which is linked to Sheets("column hide settings").Range("F2").
Now in Sheets("column hide settings").Range("F3") I have offset formula which is essentially picking up the respective value from D2:D6 of picked section.
In my real workbook I will be using dynamic named ranges for both of these tables (e.g SourceList_DYNAMIC_column_hide_settings), just to keep it as dynamic as possible so I can add/change settings on the fly.

What I'm trying to achieve is to use these values to un-hide certain columns.

Below is my current code which work to a extent:

Sub example_unhide()
Application.ScreenUpdating = False

Dim columnSection As String
columnSection = Sheets("column hide settings").Range("F3").Value

Columns("" & columnSection & "").EntireColumn.Hidden = False

Application.ScreenUpdating = True
End Sub

This code works with the Section 1 to section 4 values, but it errors out with the top one. How to fix my current code so It would work with all options.

Another option would be to loop through D3:D (Like I said I will have dynamic named range "SourceList_DYNAMIC_column_hide_settings", so if the list would grow I would like it to still work). Unfortunately I'm still terribly new in VBA syntax and looping.