Hiding / Unhiding multiple named ranges at once

[Fishboy]

Hi all,

I currently have an Excel 2010 workbook containing a number of macros to hide columns. Each column is for a specific location (arranged alphabetically) and has it's own hide button which basically hides / unhides all OTHER columns than the one with the button. For example:

Sub Location2()
Application.ScreenUpdating = False
Columns("C:C").Hidden = Not Columns("C:C").Hidden
Columns("E:AD").Hidden = Not Columns("E:AD").Hidden
End Sub

As you can see from the above the button in question is for column D, and it basically hides / unhides column C, then columns E through to AD. The next column has a button with this code:

Sub Location3()
Application.ScreenUpdating = False
Columns("C:D").Hidden = Not Columns("C:D").Hidden
Columns("F:AD").Hidden = Not Columns("F:AD").Hidden
End Sub

...which hides / unhides columns C and D, then columns F through to AD, and so on across the sheet.

This is all working fine, however I am trying to future proof the document to allow for additional columns being added. The new columns wont necessarily just be added to the end as the order is alphabetised. To account for this I would like to create named ranges for each location (column) so that when new columns are added, the macros still work without the column references needing to be updated.

Now, this is where I am having difficulty...

I would like to avoid having to make a load of mammoth macros like this:

Sub Location2()
Application.ScreenUpdating = False
Range("Location1").EntireColumn.Hidden = Not (Range("Location1").EntireColumn.Hidden)
Range("Location3").EntireColumn.Hidden = Not (Range("Location3").EntireColumn.Hidden)
Range("Location4").EntireColumn.Hidden = Not (Range("Location4").EntireColumn.Hidden)
Range("Location5").EntireColumn.Hidden = Not (Range("Location5").EntireColumn.Hidden)
Range("Location6").EntireColumn.Hidden = Not (Range("Location6").EntireColumn.Hidden)
Range("Location7").EntireColumn.Hidden = Not (Range("Location7").EntireColumn.Hidden)
Range("Location8").EntireColumn.Hidden = Not (Range("Location8").EntireColumn.Hidden)
Range("Location9").EntireColumn.Hidden = Not (Range("Location9").EntireColumn.Hidden)
Range("Location10").EntireColumn.Hidden = Not (Range("Location10").EntireColumn.Hidden)
Range("Location11").EntireColumn.Hidden = Not (Range("Location11").EntireColumn.Hidden)
Range("Location12").EntireColumn.Hidden = Not (Range("Location12").EntireColumn.Hidden)
Range("Location13").EntireColumn.Hidden = Not (Range("Location13").EntireColumn.Hidden)
Range("Location14").EntireColumn.Hidden = Not (Range("Location14").EntireColumn.Hidden)
Range("Location15").EntireColumn.Hidden = Not (Range("Location15").EntireColumn.Hidden)
Range("Location16").EntireColumn.Hidden = Not (Range("Location16").EntireColumn.Hidden)
Range("Location17").EntireColumn.Hidden = Not (Range("Location17").EntireColumn.Hidden)
Range("Location18").EntireColumn.Hidden = Not (Range("Location18").EntireColumn.Hidden)
Range("Location19").EntireColumn.Hidden = Not (Range("Location19").EntireColumn.Hidden)
Range("Location20").EntireColumn.Hidden = Not (Range("Location20").EntireColumn.Hidden)
Range("Location21").EntireColumn.Hidden = Not (Range("Location21").EntireColumn.Hidden)
Range("Location22").EntireColumn.Hidden = Not (Range("Location22").EntireColumn.Hidden)
Range("Location23").EntireColumn.Hidden = Not (Range("Location23").EntireColumn.Hidden)
Range("Location24").EntireColumn.Hidden = Not (Range("Location24").EntireColumn.Hidden)
Range("Location25").EntireColumn.Hidden = Not (Range("Location25").EntireColumn.Hidden)
Range("Location26").EntireColumn.Hidden = Not (Range("Location26").EntireColumn.Hidden)
Range("Location27").EntireColumn.Hidden = Not (Range("Location27").EntireColumn.Hidden)
Range("Location28").EntireColumn.Hidden = Not (Range("Location28").EntireColumn.Hidden)
End Sub

Is there a clever way in VBA to have code which basically identifies a specific named range and hides ALL OTHER named ranges? This way although I will still have to make a macro for each location (column), it will be a lot simpler than that beast above and easier to make new ones for any additional columns that are added.

Any help people can offer would be greatly appreciated.

Fishboy

 

[My Aswer Is This]

Glad to see your learning Vba and not just wanting others to write your scripts. There is still a lot for me to learn even though I have worked with Excel Scripting for years. There are a lot of folks here at Mr. Excel a lot smarter then me so I look at a lot of scripts here just to see new things to learn. You take care and I wish you the best

Now this is just a little snippet of my data, only showing the first 7 rows. My actual data goes on for about 60-70 rows downwards, whereas I will only even have say maybe 20-25 locations max. Changing it so my locations went down a column instead of across a row would actually be a massive ball ache and ultimately involve a time consuming rewrite of the whole thing (those values in the cells are lookups and formulas, not just raw data).

I really do appreciate the help, but I may have to look into other possible workarounds or solutions.