Deleting Hidden rows

[cape]

I am using this code
Public Sub DeleteHiddenRows()
Dim rDelete As Range
Dim rCell As Range

For Each rCell In ActiveSheet.UsedRange.Columns(1).Cells
If rCell.EntireRow.Hidden Then
If rDelete Is Nothing Then
Set rDelete = rCell
Else
Set rDelete = Union(rDelete, rCell)
End If
End If
Next rCell
If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
End Sub

I would like this to loop through all my open worksheets in a workbook
Is this possible.

Thanks in advance
Help is always appreciate

 

[Frank Kabel]

Hi
try the following

Public Sub DeleteHiddenRows()
Dim rDelete As Range
Dim rCell As Range
Dim kws as worksheet

for each wks in worksheets
For Each rCell In wks.UsedRange.Columns(1).Cells
If rCell.EntireRow.Hidden Then
If rDelete Is Nothing Then
Set rDelete = rCell
Else
Set rDelete = Union(rDelete, rCell)
End If
End If
Next rCell
If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
next
End Sub

 

[David McRitchie]

I did not test your code will it process two hidden rows
together or will it skip the 2nd hidden row.

Indenting your code might help you with readability.

For code to process an entire workbook see some
examples near top in
http://www.mvps.org/dmcritchie/excel/sheets.htm


[clipped]

 

[Dave Peterson]

I think Frank had a typo and a small bug in his code:

These minor modifications to Frank's code worked for me:

Option Explicit

Public Sub DeleteHiddenRows()
Dim rDelete As Range
Dim rCell As Range
Dim wks As Worksheet

For Each wks In Worksheets
Set rDelete = Nothing
For Each rCell In wks.UsedRange.Columns(1).Cells
If rCell.EntireRow.Hidden Then
If rDelete Is Nothing Then
Set rDelete = rCell
Else
Set rDelete = Union(rDelete, rCell)
End If
End If
Next rCell
If Not rDelete Is Nothing Then rDelete.EntireRow.Delete
Next wks

End Sub

I corrected the spelling of wks on the Dim line and I added "set rDelete =
nothing" at the start of each worksheet process.

 

[Dave Peterson]

Actually, the OP was building a giant range to delete at the end. I think you
read it that he was deleting the rows as soon as he saw that they qualified.

I did not test your code will it process two hidden rows
together or will it skip the 2nd hidden row.

Indenting your code might help you with readability.

For code to process an entire workbook see some
examples near top in
http://www.mvps.org/dmcritchie/excel/sheets.htm


[clipped]

 

[David McRitchie]

Yes, and that is a fast method of deleting rows. Thanks for heads up,
David McRitchie

 

[Frank Kabel]

Hi Dave
yes I had
thanks for the correction!

 

[cape]

Now all the codes are hanging. 99% processor. Have to task man it to ge
it to close. Any suggestions????

Thank

 

[Dave Peterson]

I bet you have lots of hidden/visible rows intermingled in your worksheets.

When excel builds those ranges, it can get clogged down.

This version can be slower, but I've never seen it fail:

Option Explicit
Public Sub DeleteHiddenRows2()

Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long
Dim wks As Worksheet
Dim calcMode As Long

With Application
calcMode = .Calculation
.ScreenUpdating = False
End With

For Each wks In Worksheets
With wks
.DisplayPageBreaks = False
FirstRow = 1
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row

For iRow = LastRow To FirstRow Step -1
If iRow Mod 50 = 0 Then
Application.StatusBar _
= "Processing row#: " & iRow & " on: " & .Name
End If
If .Rows(iRow).Hidden = True Then
.Rows(iRow).Delete
End If
Next iRow
End With
Next wks

With Application
.Calculation = calcMode
.StatusBar = False
.ScreenUpdating = True
End With

End Sub

 

[cape]

You are the man.

Thanks
Dave and Frank


Either of you know about ar odbc or maybe sorting into differen
worksheets based on a condition or multiple condition