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

 

[Webbarr]

Try this -

sub Location2()
Application.Screenupdating = False
Dim x as Integer
'change 28 based on your number of named ranges
For x = 1 To 28
Range("Location" & x).EntireColumn.Hidden Not (Range("Location" & x).EntireColumn.Hidden)

Next x

End Sub

 

[Fishboy]

Try this -

sub Location2()
Application.Screenupdating = False
Dim x as Integer
'change 28 based on your number of named ranges
For x = 1 To 28
Range("Location" & x).EntireColumn.Hidden Not (Range("Location" & x).EntireColumn.Hidden)

Next x

End Sub

Hi Webbarr, many thanks for the feedback, and sorry for the delay in my response however I have been ill and away from my office computer for the last week. Unfortunately I over simplified for the sake of explanation / example and as such your code will not work. In my example the locations were all called Location1, Location2 and so on. In my real data these locations have the names of actual places (organised alphabetically). This means that the code will not be able to use the (Range("Location" & x) part to function.

I will go back to the drawing board and see if there is another way around this. If anyone else is able to offer any suggestions that would obviously also still be appreciated.

 

[Webbarr]

I probably should have guessed that was a little simplified! I'm sorry to hear you've been unwell, I hope you're feeling better.

On the off chance - are the locations set in stone? Do you have a default list of available locations to search for?

 

[Fishboy]

On the off chance - are the locations set in stone? Do you have a default list of available locations to search for?

To a certain degree yes. I currently have 16 locations on the books, and these 16 will always be there. I was hoping to future proof the whole affair as over time more locations will be added. As far as I am aware no locations will ever be removed, so the list will constantly be growing rather than being in a flux of ups and downs. The 16 locations I currently have are as follows:

Basildon
Birmingham
Bristol
Crawley
Didcot
Glasgow
Leeds
Leicester
Livingston
London
Manchester
Milton Keynes
Newcastle
Norwich
Peterborough
Swindon

As you can see beyond being alphabetised there is no specific rhyme or reason to the list, they are just place names. There is no telling what the next location to be added will be, and therefore it could potentially fit anywhere into the list alphabetically.

 

[My Aswer Is This]

If I understand you each of the below names have one column assigned to them.
And from time to time you want to hide or unhide these columns is that correct.
And you want all the other 16 columns hidden.
So for example you just want to see the column assigned to Basildon
Basildon
Birmingham
Bristol
etc. etc.
And the list will be getting larger.
If this is correct I may have a plan for you.

 

[Fishboy]

If I understand you each of the below names have one column assigned to them.
And from time to time you want to hide or unhide these columns is that correct.
And you want all the other 16 columns hidden.
So for example you just want to see the column assigned to Basildon
Basildon
Birmingham
Bristol
etc. etc.
And the list will be getting larger.
If this is correct I may have a plan for you.

That is pretty much it, yes. Each of the listed locations has it's own column. I am looking to press , for example, the Basildon button, then all other columns are hidden / unhidden.

Yes, the list will be getting larger as new locations come onto the books. The new columns will be inserted alphabetically so not just to the end. So, if say Mapledurham was added, the new column would be added in between Manchester and Milton Keynes.

If you have any suggestions I am all ears

 

[My Aswer Is This]

Here is my suggestion. This keeps you from having to name each column.
On your worksheet put all the names like:
Basildon
Birmingham
BristBasildon
Birmingham
Bristolol
In column “A”
In column “B” put the Column number assigned this name
Like:
Bassildon 1
Birmingham 2
Now put the below script in the sheet.
To install this code:

• Right-click on the sheet tab
• Select View Code from the pop-up context menu
• Paste the code in the VBA edit window

Now when you double click on the number in column “B” it will cause that column number to be hidden. Double click it again and it will unhide that column number.
To hide all or unhide all put the text “All” in column “A” and something like this in column “B” C:J
Double clicking on C:J
Would cause columns C:J to hide or unhide.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Columns(Target.Value).Hidden = Not Columns(Target.Value).Hidden
End If
End Sub

 

[My Aswer Is This]

C:J means columns C through J

 

[My Aswer Is This]

P:S
If you would rather use column letters you could put A B C etc. in column "B"