Solved: Remember Hidden State

[MrRhodes2004]

I am working with a document FromOthers.xlsx. In this workbook, there are rows, different rows in each worksheet, which have been hidden. The rows that are hidden are different each time I see the document. I need all rows unhidden to work on the workbook. To date, I have been writing down which rows have been hidden, opening the rows, completing my changes, and then manually hiding the rows.
The workbook, FromOthers.xlsx is not allowed to have macros. Therefore, the VBA needs to be contained in another workbook, MyCode.xlsm.
I need VBA to examine every worksheet in the workbook, identify and record which rows are hidden, then unhide all rows. Once I am done making changes, I want to have the code re-hide all previously hidden rows – return it to it’s saved state.

[Kenneth Hobs]

Is that bold font? It is hard on the eyes for me.

Use a Range of SpecialCells and iterate through each Sheet and store that range into an array.

[snb]

To make an inventory of hidden rows:

Sub snb()
For Each sh In Sheets
With Columns(1).SpecialCells(12)
For j = 1 To .Areas.Count - 1
c01 = c01 & "|" & sh.Name & "_" & Range(.Areas(j).Cells(.Areas(j).Cells.Count).Offset(1), .Areas(j + 1).Cells(1).Offset(-1)).Address
Next
End With
sh.rows.hidden=false
Next

ThisWorkbook.BuiltinDocumentProperties("title") = Mid(c01, 2)
End Sub

To restore the rows to 'hidden'

Sub snb_2()
For Each it In Split(ThisWorkbook.BuiltinDocumentProperties("title").Value, "|")
Sheets(Split(it, "_")(0)).Range(Split(it, "_")(1)).EntireRow.Hidden = True
Next
End Sub

If you save this document as xlsx, these 2 macros will be removed automatically.

[MrRhodes2004]

snb!

Thanks, that is slick. That is exactly what I needed. I wish I had more time to learn more but thank you very much for a simple solution.

[mikerickson]

You could store the visible cells in the Names of the other workbook.

Sub UnhideOtherBook()
Dim oneSheet As Worksheet

For Each oneSheet In Workbooks("FromOthers.xlsx").Worksheets
With oneSheet
.Names.Add Name:="temp", RefersTo:="=" & .Columns(1).SpecialCells(xlCellTypeVisible).Address(, , , True)
.Rows.Hidden = False
End With
Next oneSheet
End Sub


Sub ReHideOtherBook()
Dim oneSheet As Worksheet

For Each oneSheet In Workbooks("fromOthers.xlsx").Worksheets
With oneSheet
.Rows.Hidden = True
.Names("temp").RefersToRange.EntireRow.Hidden = False
.Names("temp").Delete
End With
Next oneSheet

End Sub