VBA to remember current visible sheets and active cells, then unhide all hidden sheets. #2 to re-hide and set back to previous active cells

[fruehling]

Hi all,

Would love help on this. I've found lots of code online that come close to this, but I can't quite tweak it to be as simple as this should be.

I have a file with about 5-6 visible sheets and 9-10 hidden sheets. I would like to set up 2 macros.

#1. to take a look at, and remember how it is currently set up (which tabs are visible/hidden and which cells are the active cells on each), then to unhide all sheets. I was thinking an easy way might be to create a new sheet to temporarily store this info.

#2. to put everything back to how it was before the sheets were unhidden.

Can anyone help?

 

[Legacy 320475]

No need to apologize at all. I'm greatful for your help. The edit worked great! I have some protection, so I had to add that in and account for that.

One last thing I forgot to mention, and I really hope it's easy to take care of. There are some hidden rows/columns on the sheets that i'm unhiding in the first step with this:

    Sheet1.Rows.EntireRow.Hidden = False
    Sheet1.Columns.EntireColumn.Hidden = False

However, upon rehiding the sheets, I need to also rehide the rows and columns that were hidden when the file was opened. Any clever ideas how to handle that? Would it be best to record that on the setup tab as well somehow?

Is there any pattern to your hidden rows and columns or any headers or text that would be useful?

 

[fruehling]

Is there any pattern to your hidden rows and columns or any headers or text that would be useful?

Sadly not. Its different for each.

 

[fruehling]

Sadly not. Its different for each.

I did just now find this code online. I haven't had a chance to test it yet.

[Function HiddenRows(Optional WS As Worksheet) As String
 Dim UnusedCol As Long, LastRow As Long, UnusedColStatus As Boolean
 If WS Is Nothing Then Set WS = ActiveSheet
 UnusedCol = WS.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
 SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
 Application.ScreenUpdating = False
 On Error GoTo Done
 With WS.Columns(UnusedCol)
 UnusedColStatus = .Hidden
 .Hidden = False
 .Value = "X"
 .SpecialCells(xlCellTypeVisible).Clear
 HiddenRows = .SpecialCells(xlCellTypeConstants).EntireRow.Address(0, 0)
 .Clear
 .Hidden = UnusedColStatus
 End With
Done:
 Application.ScreenUpdating = True
End Function
Function HiddenColumns(Optional WS As Worksheet)
 Dim UnusedRow As Long, LastRow As Long, UnusedRowStatus As Boolean
 If WS Is Nothing Then Set WS = ActiveSheet
 UnusedRow = WS.Cells.Find(What:="*", SearchOrder:=xlRows, _
 SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row + 1
 Application.ScreenUpdating = False
 On Error GoTo Done
 With WS.Rows(UnusedRow)
 UnusedRowStatus = .Hidden
 .Hidden = False
 .Value = "X"
 .SpecialCells(xlCellTypeVisible).Clear
 HiddenColumns = .SpecialCells(xlCellTypeConstants).EntireColumn.Address(0, 0)
 .Clear
 .Hidden = UnusedRowStatus
 End With
Done:
 Application.ScreenUpdating = True
End Function

Think this may help?

 

[Legacy 320475]

I did just now find this code online. I haven't had a chance to test it yet.

[Function HiddenRows(Optional WS As Worksheet) As String
 Dim UnusedCol As Long, LastRow As Long, UnusedColStatus As Boolean
 If WS Is Nothing Then Set WS = ActiveSheet
 UnusedCol = WS.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
 SearchDirection:=xlPrevious, LookIn:=xlFormulas).Column + 1
 Application.ScreenUpdating = False
 On Error GoTo Done
 With WS.Columns(UnusedCol)
 UnusedColStatus = .Hidden
 .Hidden = False
 .Value = "X"
 .SpecialCells(xlCellTypeVisible).Clear
 HiddenRows = .SpecialCells(xlCellTypeConstants).EntireRow.Address(0, 0)
 .Clear
 .Hidden = UnusedColStatus
 End With
Done:
 Application.ScreenUpdating = True
End Function
Function HiddenColumns(Optional WS As Worksheet)
 Dim UnusedRow As Long, LastRow As Long, UnusedRowStatus As Boolean
 If WS Is Nothing Then Set WS = ActiveSheet
 UnusedRow = WS.Cells.Find(What:="*", SearchOrder:=xlRows, _
 SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row + 1
 Application.ScreenUpdating = False
 On Error GoTo Done
 With WS.Rows(UnusedRow)
 UnusedRowStatus = .Hidden
 .Hidden = False
 .Value = "X"
 .SpecialCells(xlCellTypeVisible).Clear
 HiddenColumns = .SpecialCells(xlCellTypeConstants).EntireColumn.Address(0, 0)
 .Clear
 .Hidden = UnusedRowStatus
 End With
Done:
 Application.ScreenUpdating = True
End Function

Think this may help?

Seems fine to me in principle. I was trying something similar, I was trying to put "Hidden" in every hidden cell (instead of x), but couldn't get far enough without something more specific. It looks like that could help. I was also worried about using .Find as it can be time-consuming, but if there's no pattern to your hidden rows and columns, that could be the best answer.

 

[fruehling]

Seems fine to me in principle. I was trying something similar, I was trying to put "Hidden" in every hidden cell (instead of x), but couldn't get far enough without something more specific. It looks like that could help. I was also worried about using .Find as it can be time-consuming, but if there's no pattern to your hidden rows and columns, that could be the best answer.

Awesome. Thanks! When I get back to work tomorrow, I'll try implementing this. If it doesn't work, would you mind if I post back here for your help in getting it running?

 

[fruehling]

Seems fine to me in principle. I was trying something similar, I was trying to put "Hidden" in every hidden cell (instead of x), but couldn't get far enough without something more specific. It looks like that could help. I was also worried about using .Find as it can be time-consuming, but if there's no pattern to your hidden rows and columns, that could be the best answer.

Ok, so back at work. I hadn't created a function before, so I'm learning here. On the Setup sheet I hid column X,Y,Z and row 98,99,100.

I dropped the code into a module, and on the Setup sheet I just put a formula into a cell =hiddenrows("Setup"), but the result was #VALUE!, I also tried with a reference to a cell that contained the word Setup with the same result.

I also tried =hiddenrows() which I believe should use activesheet. Ths result is 0.

So, I think I must be doing something wrong here. Can you help me out?

 

[fruehling]

No luck here still. I'm banging my head trying to work this out. Any chance to can help me out with this last piece?