Delete Hidden rows?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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)
Chris said:
I'm working with 59,000+ records and have filtered them (to hide the
Click to expand...
repeating) and wanted to know if there is a macro to delete hidden rows. TIA
 
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
 
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
 
Back
Top