Automatically Hide Unused Columns (and how to unhide them!)

[manona]

Hi,

I have a question in regards to automatically hiding unused columns.

I have several columns for each fiscal year, and have several fiscal years in my workbook. Is there any way to hide the columns fiscal years that are unused? Unused may be blank or with formulas but 0 value. Up to you!

Also... how to automatically unhide these when the time comes.

Thank you so much for your time and help, it's really appreciated!

 

[tygrrboi]

If you name the Header cells for the entire range of a fiscal year then you can hide or unhide them by name...

For example I have content with Fiscal Year 1 in columns A through D... and Fiscal Year 2 in E through H... The headers are in row 2...

I would name A2:D2 as "FiscalYear1" and E2:H2 as "FiscalYear2" or whatever you want to call them...

Then in VBA you can call the range by name.

Range("FiscalYear1").EntireColumn.Hidden = true

To add a bit of complexity to it... one way you could handle it would be the following.

Sub OpenOneFiscalYear()

'or hide them all... 
Dim i as Long
Dim DesiredYear as Long

On Error goto NotAYear

'ask the user in an input box what year to view... (one way to do it)
DesiredYear = InputBox("What Fiscal Year would you like to view?")

For i = 1 to 10 'or however many years you have named
   Range("FiscalYear" & i).EntireColumn.Hidden = True
Next i

'then unhide just one...
Range("FiscalYear" & DesiredYear).EntireColumn.Hidden = False

Exit Sub

NotAYear:
msgbox("Error: That selection is not a designated fiscal year..."

End Sub