Detecting Hidden Rows

@J.Woolley

Thank you so much for your help, that works great - just what I needed

Steve

@Steve Fenn
Re. s = InputBox(...), if s is like "1-3,6-10" you need two Split statements (first on "," then on "-") and two For statements. Try this instead:

    sTemp = sTemp & vbCrLf & vbCrLf & "Enter rows to unhide " _
        & "separated by comma; use colon for adjacent rows. " _
        & "For example, 1:3,6:9"
    s = InputBox(sTemp, "HiddenRows")
    x = Split(s, ",")
    For k = LBound(x) To UBound(x)
        Rows(x(k)).Hidden = False
    Next k

Note: The prompt in VBA's InputBox(prompt,...) and MsgBox(prompt,...) is limited to ~1,024 characters.
Also, see https://sites.google.com/view/MyExcelToolbox/

@J. Woolley
I amended the code so that you can determine which rows you wnt to hide or unhide by using an Input Box. The Input uses Split and a "-" to seperate the rows. As I said I sometimes need to choose rows that are not contiguous, such as 1-3 and 6-10, still leaving 4 & 5 hidden.

Code follows:

Sub ShowRows()
'Mod14
Dim rng As Range
Dim c As Range
Dim sTemp As String
Dim x
Dim s As String, i As Long, j As Long, k As Long

On Error GoTo Nowt
ActiveSheet.Unprotect Password:="666892"

Set rng = Range("A12:A27, A33:A42,A44:A70")
sTemp = ""

For Each c In rng
If c.EntireRow.Hidden Then
'sTemp = sTemp & "Row " & c.Row & vbCrLf
sTemp = sTemp & "Row " & c.Row & " - " & c.Value & vbCrLf
End If
Next c

If sTemp > "" Then
sTemp = "The following rows are hidden:" & vbCrLf & _
vbCrLf & sTemp
'MsgBox sTemp, vbInformation, "Hidden Rows"

s = InputBox(sTemp & vbCrLf & vbCrLf & "Enter Rows To Unhide Seperated by -", "HiddenRows")
x = Split(s, "-")
i = x(LBound(x))
j = x(UBound(x))
For k = i To j
Rows(k).Hidden = False
Next k
ActiveSheet.Protect Password:="666892"
Exit Sub
'User has failed to enter any rows or clicked cancel
Nowt:
MsgBox "Nothing Has Been Entered or Cancel Was Selected"
ActiveSheet.Protect Password:="666892"
Else
MsgBox "There are no hidden rows", vbInformation, "Hidden Rows"
End If
End Sub

@Steve Fenn
You said, "If I put 1-3, 5-10 it hides 1 to 10." Where do you put?
If I select rows 1:3 and 5:10 then press Ctrl+9 to hide them, row 4 remains unhidden next to row 11.
To unhide rows 1:3, see https://excelribbon.tips.net/T011104_Displaying_a_Hidden_First_Row.html
Also, see https://www.ablebits.com/office-addins-blog/hide-unhide-rows-excel/

@J.Woolley is there anyway the selection can be for non-contiguous rows. For example if you want to hide rows 1-3 and 5-10.
If I put 1-3, 5-10 it hides 1 to 10.

Thanks Steve

Brilliant @J Wooley, thank you so much. Steve

@Steve Fenn
Replace this line
sTemp = sTemp & "Row " & c.Row & vbCrLf
with this line
sTemp = sTemp & "Row " & c.Row & " - " & c.Value & vbCrLf

Brilliant really helped, but is there a way of showing the contents of the cell in Column A that relates to the hidden rows.

For example cell A7 = "Sales UK" if we hide row 7 and use your macro to see that row 7 is hidden, how can we also see the contents of A7?

So the MsgBox would state The following Rows are hidden Row 7 - Sales UK

Many thanks

Steve

It should be noted that ListHiddenRowsCols (see my previous comment) must be used with newer versions of Excel (2021+) that support dynamic array functions. It doesn't work with My Excel Toolbox's SpillArray function in a cell formula; I don't know why yet. However, it could by called by a macro (Sub) with results in a MsgBox or posted to a worksheet.

In the VBA code of my previous comment for the simplified version of ListHiddenRowsCols, this statement
    With ActiveSheet
should be replaced by
    With Application.Caller.Worksheet
This assumes the function will be used as a UDF in a cell formula.

My Excel Toolbox includes the following function to return a list of hidden rows and columns as a dynamic array:
=ListHiddenRowsCols([AllSheets])
If AllSheets is FALSE (default), only the current worksheet will be evaluated; if TRUE, all worksheets in the workbook will be included.
This function considers the "used" range from cell A1 to the last non-blank cell or the last formatted blank cell (Ctrl+End). It normally returns hidden rows beyond the used range, but it returns hidden columns beyond the used range only if they were adjacent to the used range before they were hidden; such columns are added to the used range even if they are subsequently unhidden. The used range is reset when the workbook is closed and reopened.
Here is a simplified version of the function:

Public Function ListHiddenRowsCols() As String()
    Dim A() As String, B As String, C As Collection, n As Long
    Application.Volatile
    B = "[" & ActiveWorkbook.Name & "]"
    Set C = New Collection
    C.Add "Hidden Rows/Cols"
    With ActiveSheet
        ListHiddenRowsCols_Do B, .Rows, _
            (.UsedRange.Row + .UsedRange.Rows.Count), C
        ListHiddenRowsCols_Do B, .Columns, _
            (.UsedRange.Column + .UsedRange.Columns.Count), C
    End With
    If C.Count = 1 Then C.Add "None"
    ReDim A(1 To C.Count, 1 To 1)
    For n = 1 To C.Count
        A(n, 1) = C(n)
    Next n
    ListHiddenRowsCols = A
End Function

Private Sub ListHiddenRowsCols_Do(Book As String, _
    Obj As Object, Nbr As Long, ByRef C As Collection)
    Dim nBeg As Long, nEnd As Long, n As Long
    nBeg = 0
    For n = 1 To Nbr
        If Obj(n).Hidden Then
            If nBeg = 0 Then
                nBeg = n
                nEnd = n
            ElseIf n = nEnd + 1 Then
                nEnd = n
            End If
        ElseIf nBeg > 0 Then
            C.Add Replace(Application.Range(Obj(nBeg), _
                Obj(nEnd)).Address(External:=True), _
                Book, "", 1, 1)
            nBeg = 0
        End If
    Next n
End Sub

See https://sites.google.com/view/MyExcelToolbox/

I noticed that when you put the color in and you still have it selected with the ALT+; there are breaks in the color where the hidden rows are located. Very helpful for finding the hidden without un-hiding the rows.

I also adapted the code to do the same for hidden columns and then both columns and rows.

Nice lesson, Thanks