Unhiding hidden rows in Excel

Question

Here's my current code. If hidden rows are found, it puts red borders around the rows, a MsgBox details which rows were hidden, and the hidden rows are resized to a height of 15. It works perfectly unless the rows hidden are the last rows in the range. If they are the last rows in the range, this code unhides them but the red border does not get applied and the MsgBox doesn't include those rows in the reported list of hidden rows.

Is this because this method of finding the last row doesn't work if the last row is hidden? Or is the method acceptable and I just need to add/change something?

Sub UnhideRows()
Dim LastRow As Integer
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    Dim rng As Range
    Dim r As Range
    Dim sTemp As String

    Set rng = Range("A84:A" & LastRow)
    sTemp = ""
    For Each r In rng.Rows
        If r.EntireRow.Hidden = True Then
            sTemp = sTemp & "Row " & Mid(r.Address, 4) & vbCrLf
            r.EntireRow.Hidden = False
                With Range("A" & r.Row & ":W" & r.Row).Borders(xlEdgeLeft)
                     .Color = -16776961
                     .Weight = xlMedium
                End With

                With Range("A" & r.Row & ":W" & r.Row).Borders(xlEdgeTop)
                     .Color = -16776961
                     .Weight = xlMedium
                End With

                With Range("A" & r.Row & ":W" & r.Row).Borders(xlEdgeBottom)
                     .Color = -16776961
                     .Weight = xlMedium
                End With

                With Range("A" & r.Row & ":W" & r.Row).Borders(xlEdgeRight)
                     .Color = -16776961
                     .Weight = xlMedium
                End With
        End If
    Next r

     If sTemp <> "" Then
        sTemp = "The following rows were hidden:" & vbCrLf & _
          vbCrLf & sTemp
          MsgBox sTemp
     Else

     End If

         Cells.rowheight = 15
End Sub

QHarr · Answer 1 · 2018-04-16T17:47:51.777

Try

Option Explicit

Public Sub UnhideRows()
    Dim LastRow As Long, rng As Range, r As Range, sTemp As String

    With ActiveSheet
        LastRow = .Cells.Find("*", [a1], xlFormulas, , xlByRows, xlPrevious).Row
        Set rng = .Range("A84:A" & LastRow)
        sTemp = vbNullString

        For Each r In rng.Rows
            If r.EntireRow.Hidden Then
                sTemp = sTemp & "Row " & Mid(r.Address, 4) & vbCrLf
                r.EntireRow.Hidden = False

                With .Range("A" & r.Row & ":W" & r.Row).Borders(xlEdgeLeft)
                    .Color = -16776961
                    .Weight = xlMedium
                End With
                With .Range("A" & r.Row & ":W" & r.Row).Borders(xlEdgeTop)
                    .Color = -16776961
                    .Weight = xlMedium
                End With
                With .Range("A" & r.Row & ":W" & r.Row).Borders(xlEdgeBottom)
                    .Color = -16776961
                    .Weight = xlMedium
                End With
                With .Range("A" & r.Row & ":W" & r.Row).Borders(xlEdgeRight)
                    .Color = -16776961
                    .Weight = xlMedium
                End With
            End If
        Next r

        If sTemp <> vbNullString Then
            sTemp = "The following rows were hidden:" & vbCrLf & _
                    vbCrLf & sTemp
            MsgBox sTemp
        End If
        .Cells.RowHeight = 15
    End With
End Sub

Which I would initially refactor into to benefit from using Union to handle all rows in one go.

Option Explicit

Public Sub UnhideRows()
    Dim LastRow As Long, rng As Range, r As Range, sTemp As String, unionRng As Range, borders(), i As Long
    With ActiveSheet
        LastRow = .Cells.Find("*", [a1], xlFormulas, , xlByRows, xlPrevious).Row
        Set rng = .Range("A84:A" & LastRow)
        sTemp = vbNullString

        For Each r In rng.Rows
            If r.EntireRow.Hidden Then
                sTemp = sTemp & "Row " & Mid(r.Address, 4) & vbCrLf
                If Not unionRng Is Nothing Then
                    Set unionRng = Union(unionRng, r.Resize(1, 23))
                Else
                    Set unionRng = r.Resize(1, 23)
                End If
            End If
        Next r

        If Not unionRng Is Nothing Then
            With unionRng
                .EntireRow.Hidden = False
                .borders(xlEdgeLeft).Color = -16776961
                .borders(xlEdgeLeft).Weight = xlMedium
                .borders(xlEdgeTop).Color = -16776961
                .borders(xlEdgeTop).Weight = xlMedium
                .borders(xlEdgeBottom).Color = -16776961
                .borders(xlEdgeBottom).Weight = xlMedium
                .borders(xlEdgeRight).Color = -16776961
                .borders(xlEdgeRight).Weight = xlMedium
            End With
        End If

        If sTemp <> vbNullString Then
            sTemp = "The following rows were hidden:" & vbCrLf & _
                    vbCrLf & sTemp
            MsgBox sTemp
        End If
        .Cells.RowHeight = 15
    End With
End Sub

The lower version should be faster. – QHarr Apr 16 '18 at 17:48 — QHarr, Apr 16 '18 at 17:48

score 1 · Answer 2 · answered Apr 16 '18 at 17:04

Take this function found here

Function FindLastRow(R As Range) As Long
    Const NotFoundResult = 1 ' If all cells have an empty value, this value is returned
    FindLastRow = R.Worksheet.Evaluate("IFERROR(LARGE(ROW('" & R.Worksheet.Name & "'!" & R.Address & ")*--('" & R.Worksheet.Name & "'!" & R.Address & " <> """"),1)," & NotFoundResult & ")")
End Function

and adjust your code like that

LastRow = FindLastRow(ActiveSheet.Range("A:A"))

LFB · Accepted Answer · 2018-04-16T17:21:35.963

Indeed that seems to be the case. The method to find the last row skips hidden rows.

I think changing LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row to

With ActiveSheet.Cells(Rows.Count, 1).End(xlUp)
    If .Offset(1).EntireRow.Hidden = True Then
        LastRow = .Offset(1).Row
    Else
        LastRow = .Row
    End If
End With

will do the trick

EDIT If more than 2 rows could be hidden at the end of the range:

With ActiveSheet.Cells(Rows.Count, 1).End(xlUp)
For hidden_ones = 0 To ActiveSheet.Rows.Count
    If .Offset(hidden_ones + 1).EntireRow.Hidden = False Then Exit For
Next hidden_ones
    LastRow = .Offset(hidden_ones).Row
End With

This is better, but if the last 2 rows for example are hidden in the range, it only applies the border and mentions the first hidden row. — Robby, Apr 16 '18 at 17:13

Unhiding hidden rows in Excel

3 Answers3