Fishboy
Well-known Member
- Joined
- Feb 13, 2015
- Messages
- 4,267
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:
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:
...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:
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
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:
Code:
Sub Location2()
Application.ScreenUpdating = False
Columns("C:C").Hidden = Not Columns("C:C").Hidden
Columns("E:AD").Hidden = Not Columns("E:AD").Hidden
End SubAs 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:
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:
Code:
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 SubIs 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
