Consulting

Results 1 to 5 of 5

Thread: Solved: Remember Hidden State

  1. #1

    Solved: Remember Hidden State

    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.

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    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.

  3. #3
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    To make an inventory of hidden rows:

    [VBA]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[/VBA]

    To restore the rows to 'hidden'

    [VBA]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[/VBA]

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

  4. #4
    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.

  5. #5
    Mac Moderator VBAX Guru mikerickson's Avatar
    Joined
    May 2007
    Location
    Davis CA
    Posts
    2,778
    You could store the visible cells in the Names of the other workbook.

    [VBA]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
    [/VBA]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •