Home Unhiding hidden rows in Excel
Reply: 0

Unhiding hidden rows in Excel

user2067
1#
user2067 Published in May 20, 2018, 11:57 am

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
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.308804 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO