Delete Hidden rows?

[Guest]

I'm working with 59,000+ records and have filtered them (to hide the repeating) and wanted to know if there is a macro to delete hidden rows. TIA

-Chris

 

[Don Guillett]

one way that might work
for each r in activesheet.rows
if r.hidden=true then rows(r).delete
next

--
Don Guillett
SalesAid Software
(e-mail address removed)

I'm working with 59,000+ records and have filtered them (to hide the

repeating) and wanted to know if there is a macro to delete hidden rows. TIA

 

[JE McGimpsey]

That won't work since r will contain a range, so rows(r) will depend on
the value of the first cell in the range. It will also skip every other
row in a contiguous range of hidden rows.

Try:

Public Sub DeleteHidden()
Dim rRow As Range
Dim rDelete As Range
For Each rRow In ActiveSheet.UsedRange.Rows
If rRow.Hidden Then
If rDelete Is Nothing Then
Set rDelete = rRow
Else
Set rDelete = Union(rDelete, rRow)
End If
End If
Next rRow
If Not rDelete Is Nothing Then
Application.DisplayAlerts = False
rDelete.Delete
Application.DisplayAlerts = True
End If
End Sub

 

[Don Guillett]

Didn't work but this should

Sub delrows()
For i = 21 To 2 Step -1
If Rows(i).Hidden = True Then Rows(i).Delete
Next
End Sub