Is there a way to delete all hidden rows in a worksheet?

[panda4444]

Is there a way to delete all hidden rows in a worksheet?

panda

 

[phantom1975]

This macro did the trick for me:

Sub Delete_Hidden()
Dim r As Long
    For r = 1 To 100
    'This will check to row 100
        If Rows(r).Hidden = True Then
            Rows(r).EntireRow.Delete
            r = r - 1
        End If
    Next r
End Sub

 

[Mudface]

Bit more convoluted but probably faster if you have a lot of rows: -

Public Sub DeleteHiddenRows()
Dim rngVisible As Range
Dim rngAll As Range

On Error Resume Next ' In case there are no hidden/ visible rows
Application.ScreenUpdating = False
Set rngVisible = Range("A:A").SpecialCells(xlCellTypeVisible)
Set rngAll = Range("A:A")


With rngAll
    .EntireRow.Hidden = False ' Unhide all rows
    rngVisible.EntireRow.Hidden = True ' Hide rows originally visible
    .SpecialCells(xlCellTypeVisible).EntireRow.Delete ' Delete 'flipped' rows
    .EntireRow.Hidden = False ' Unhide all rows
End With
Application.ScreenUpdating = True

On Error GoTo 0

End Sub