ExcelBanter - Slow Performance Hiding Rows

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Slow Performance Hiding Rows (https://www.excelbanter.com/excel-programming/332554-slow-performance-hiding-rows.html)

Fid

Slow Performance Hiding Rows

I have a loop hiding blank rows. It just seems to run very slowly.
What can I do to increase the speed of this code?

Private Sub Worksheet_Activate()
Dim rng As Range
Set rng = Range(Cells(6, 7), Cells(6, 7).End(xlDown))
Hide_Rows rng

End Sub

Public Sub Hide_Rows(rng)

Application.ScreenUpdating = False

Dim r As Range
Dim row As Long

rng.Select
For Each r In Selection
r.Select
row = ActiveCell.row
Application.StatusBar = "Row " & row
If IsEmpty(r) Or r = "None" Then
Selection.EntireRow.Hidden = True
Else
Selection.EntireRow.Hidden = False
End If
Next r
Application.StatusBar = False
Application.ScreenUpdating = True

End Sub

Thanks,

Brent

Jim Cone

Slow Performance Hiding Rows

Brent,

This is a little simpler and should run much faster.
It works from the bottom up, turns off the display
of page breaks and doesn't select anything.

'-----------------------
Public Sub Hide_Rows(ByRef rng As Excel.Range)
Dim r As Long

Application.ScreenUpdating = False
For r = rng.Rows.Count To 1 Step -1
Application.StatusBar = "Row " & r
ActiveSheet.DisplayPageBreaks = False
If Len(rng(r).Value) = 0 Or rng(r).Value = "None" Then
rng(r).EntireRow.Hidden = True
Else
rng(r).EntireRow.Hidden = False
End If
Next r
Set rng = Nothing
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub
'------------------------

Jim Cone
San Francisco, USA

"Fid" wrote in message
oups.com...
I have a loop hiding blank rows. It just seems to run very slowly.
What can I do to increase the speed of this code?

Private Sub Worksheet_Activate()
Dim rng As Range
Set rng = Range(Cells(6, 7), Cells(6, 7).End(xlDown))
Hide_Rows rng
End Sub

Public Sub Hide_Rows(rng)
Application.ScreenUpdating = False
Dim r As Range
Dim row As Long
rng.Select
For Each r In Selection
r.Select
row = ActiveCell.row
Application.StatusBar = "Row " & row
If IsEmpty(r) Or r = "None" Then
Selection.EntireRow.Hidden = True
Else
Selection.EntireRow.Hidden = False
End If
Next r
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub
Thanks,
Brent

Tom Ogilvy

Slow Performance Hiding Rows

Public Sub Hide_Rows(rng)

Application.ScreenUpdating = False

Dim r As Range
rng.EntireRow.Hidden = False
'rng.Select
For Each r In rng
'r.Select
'row = ActiveCell.row
if r.row mod 20 = 0 then
Application.StatusBar = "Row " & r.row
End if
If IsEmpty(r) Or r = "None" Then
r.EntireRow.Hidden = True
End If
Next r
Application.StatusBar = False
Application.ScreenUpdating = True

End Sub

--
Regards,
Tom Ogilvy

"Fid" wrote in message
oups.com...
I have a loop hiding blank rows. It just seems to run very slowly.
What can I do to increase the speed of this code?

Private Sub Worksheet_Activate()
Dim rng As Range
Set rng = Range(Cells(6, 7), Cells(6, 7).End(xlDown))
Hide_Rows rng

End Sub

Public Sub Hide_Rows(rng)

Application.ScreenUpdating = False

Dim r As Range
Dim row As Long

rng.Select
For Each r In Selection
r.Select
row = ActiveCell.row
Application.StatusBar = "Row " & row
If IsEmpty(r) Or r = "None" Then
Selection.EntireRow.Hidden = True
Else
Selection.EntireRow.Hidden = False
End If
Next r
Application.StatusBar = False
Application.ScreenUpdating = True

End Sub

Thanks,

Brent

K Dales[_2_]

Slow Performance Hiding Rows

First, your selection can in some circumstances extend to the end of the
sheet (such as ALL cells below the specified cell are blank). I would use
this to avoid that problem:
Set rng = Range(Cells(6, 7), Columns(7).SpecialCells (xlCellTypeLastCell))
Then, (minor point) no need to select r; just use row = r.Row and
r.EntireRow.Hidden. Also, since you use r repeatedly, using With r... End
With will be faster.

Those are the obvious things I can see that might help. The first one is
the main one: if your code had to loop through over 65000 rows and check and
hide them all, well, I bet it was indeed pretty slow!

"Fid" wrote:

I have a loop hiding blank rows. It just seems to run very slowly.
What can I do to increase the speed of this code?

Private Sub Worksheet_Activate()
Dim rng As Range
Set rng = Range(Cells(6, 7), Cells(6, 7).End(xlDown))
Hide_Rows rng

End Sub

Public Sub Hide_Rows(rng)

Application.ScreenUpdating = False

Dim r As Range
Dim row As Long

rng.Select
For Each r In Selection
r.Select
row = ActiveCell.row
Application.StatusBar = "Row " & row
If IsEmpty(r) Or r = "None" Then
Selection.EntireRow.Hidden = True
Else
Selection.EntireRow.Hidden = False
End If
Next r
Application.StatusBar = False
Application.ScreenUpdating = True

End Sub

Thanks,

Brent

Tom Ogilvy

Slow Performance Hiding Rows

Might want to move the DisplayPageBreaks command up above the loop - good
suggestion.

--
Regards,
Tom Ogilvy

"Jim Cone" wrote in message
...
Brent,

This is a little simpler and should run much faster.
It works from the bottom up, turns off the display
of page breaks and doesn't select anything.

'-----------------------
Public Sub Hide_Rows(ByRef rng As Excel.Range)
Dim r As Long

Application.ScreenUpdating = False
For r = rng.Rows.Count To 1 Step -1
Application.StatusBar = "Row " & r
ActiveSheet.DisplayPageBreaks = False
If Len(rng(r).Value) = 0 Or rng(r).Value = "None" Then
rng(r).EntireRow.Hidden = True
Else
rng(r).EntireRow.Hidden = False
End If
Next r
Set rng = Nothing
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub
'------------------------

Jim Cone
San Francisco, USA

"Fid" wrote in message
oups.com...
I have a loop hiding blank rows. It just seems to run very slowly.
What can I do to increase the speed of this code?

Private Sub Worksheet_Activate()
Dim rng As Range
Set rng = Range(Cells(6, 7), Cells(6, 7).End(xlDown))
Hide_Rows rng
End Sub

Public Sub Hide_Rows(rng)
Application.ScreenUpdating = False
Dim r As Range
Dim row As Long
rng.Select
For Each r In Selection
r.Select
row = ActiveCell.row
Application.StatusBar = "Row " & row
If IsEmpty(r) Or r = "None" Then
Selection.EntireRow.Hidden = True
Else
Selection.EntireRow.Hidden = False
End If
Next r
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub
Thanks,
Brent

Jim Cone

Slow Performance Hiding Rows

Tom,
I agree, DisplayPageBreaks does belong above the loop.
Regards,
Jim Cone

"Tom Ogilvy" wrote in message
...
Might want to move the DisplayPageBreaks command up above the loop - good
suggestion.
Regards,
Tom Ogilvy

"Jim Cone" wrote in message
...
Brent,

This is a little simpler and should run much faster.
It works from the bottom up, turns off the display
of page breaks and doesn't select anything.

'-----------------------
Public Sub Hide_Rows(ByRef rng As Excel.Range)
Dim r As Long

Application.ScreenUpdating = False
For r = rng.Rows.Count To 1 Step -1
Application.StatusBar = "Row " & r
ActiveSheet.DisplayPageBreaks = False
If Len(rng(r).Value) = 0 Or rng(r).Value = "None" Then
rng(r).EntireRow.Hidden = True
Else
rng(r).EntireRow.Hidden = False
End If
Next r
Set rng = Nothing
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub
'------------------------

Jim Cone
San Francisco, USA

[email protected]

Slow Performance Hiding Rows

Iterate through the UsedRange as before then use this to hide all rows
after the UsedRange:
rng = Me.UsedRange.Rows(Me.UsedRange.Rows.Count).Row + 1 & ":" &
65536
Me.Range(rng).RowHeight = 0

All times are GMT +1. The time now is 12:31 PM.