ExcelBanter - Macro speed

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

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

- - Macro speed (https://www.excelbanter.com/excel-programming/441457-macro-speed.html)

The code shown below runs through about 6000 rows and takes 5 minutes or so
to sort the data and hide the rows. Does anyone know how to change this to
make it run faster?
Thanks for your help.

Dim R As Range
For Each R In Range("c7:c6210")
If R.Value = "0" Then Rows(R.Row).Hidden = True
If R.Value = "" Then Rows(R.Row).Hidden = True
Next

--
Rex Munn

There are (at least) two things you can do to increase the performance
of the code. First, don't hide each row individually. Instead, store
the references to the rows in a Range type variable and then hide that
object in one operation. In other words, instead of hiding 1000 rows
with 1000 hide operations, use 1 single hide operation. Also, turn off
screen updating so that Excel doesn't have to repaint the screen each
time something is hidden.

The following code illustrates both of these concepts:

Dim HideRows As Range
Dim N As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
For N = 1 To 1000 Step 2
If HideRows Is Nothing Then
Set HideRows = Rows(N)
Else
Set HideRows = Application.Union(HideRows, Rows(N))
End If
Next N
HideRows.EntireRow.Hidden = True
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

On Thu, 8 Apr 2010 12:57:01 -0700, Rex
wrote:

The code shown below runs through about 6000 rows and takes 5 minutes or so
to sort the data and hide the rows. Does anyone know how to change this to
make it run faster?
Thanks for your help.

Dim R As Range
For Each R In Range("c7:c6210")
If R.Value = "0" Then Rows(R.Row).Hidden = True
If R.Value = "" Then Rows(R.Row).Hidden = True
Next

Hey Rex! Here's a timer with Don's code inside:
Sub hiderowsif()

Dim sngStart As Double
sngStart = Now

Dim lr As Long
lr = Cells(Rows.Count, "c").End(xlUp).Row
Range("C1:c" & lr).AutoFilter Field:=1, _
Criteria1:="<0", Operator:=xlAnd, Criteria2:="<"

MsgBox "Update Complete. " & Counter & _
" Files Updated" & vbNewLine & _
" took " & Format(Now - sngStart, "hh:mm:ss")

End Sub

It completed in 0 seconds on my ThinkPad.

The code you posted, Rex, finished in 30 seconds.
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

"Chip Pearson" wrote:

There are (at least) two things you can do to increase the performance
of the code. First, don't hide each row individually. Instead, store
the references to the rows in a Range type variable and then hide that
object in one operation. In other words, instead of hiding 1000 rows
with 1000 hide operations, use 1 single hide operation. Also, turn off
screen updating so that Excel doesn't have to repaint the screen each
time something is hidden.

The following code illustrates both of these concepts:

Dim HideRows As Range
Dim N As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
For N = 1 To 1000 Step 2
If HideRows Is Nothing Then
Set HideRows = Rows(N)
Else
Set HideRows = Application.Union(HideRows, Rows(N))
End If
Next N
HideRows.EntireRow.Hidden = True
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

On Thu, 8 Apr 2010 12:57:01 -0700, Rex
wrote:

The code shown below runs through about 6000 rows and takes 5 minutes or so
to sort the data and hide the rows. Does anyone know how to change this to
make it run faster?
Thanks for your help.

Dim R As Range
For Each R In Range("c7:c6210")
If R.Value = "0" Then Rows(R.Row).Hidden = True
If R.Value = "" Then Rows(R.Row).Hidden = True
Next
.

"Rex" wrote:
The code shown below runs through about 6000 rows
and takes 5 minutes

I was able to duplicate your 5-min runtime by filling A1:Z7000 with =RAND().

While a completely different algorithm might be a good idea, I was able to
reduce that time to about 0.6 sec by applying just a couple simple principles
to your algorithm:

1. Disable certain actions that might occur as each row is hidden.

2. Hide rows from the bottom up, not from the top down.

#1 reduced the runtime to about 1.5 sec. #2 reduced the runtime to about
0.6 sec.

Of course, your times might be different. But I would expect comparable or
better improvement, considering my pervasive use of RAND.

Also note the correction, which I presume is your intention: If-Then-Else
instead of If/If.

The Range.Offset expression could be done a better way. I wrote it that way
to make it easier for you to relate to your original algorithm.

My macro....

Option Explicit

Sub doit()
Dim R As Range, i As Long
'more robust: save the original states in variant
'variables and restore them at the end
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
For i = 6210 To 7 Step -1
Set R = Range("c1").Offset(i - 1, 0)
If R.Value = "0" Then Rows(R.Row).Hidden = True _
Else If R.Value = "" Then Rows(R.Row).Hidden = True
Next
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With
End Sub

----- original message -----

"Rex" wrote:
The code shown below runs through about 6000 rows and takes 5 minutes or so
to sort the data and hide the rows. Does anyone know how to change this to
make it run faster?
Thanks for your help.

Dim R As Range
For Each R In Range("c7:c6210")
If R.Value = "0" Then Rows(R.Row).Hidden = True
If R.Value = "" Then Rows(R.Row).Hidden = True
Next

--
Rex Munn

Thanks to all you guys for your help.
Problem solved.
--
Rex Munn

"Joe User" wrote:

"Rex" wrote:
The code shown below runs through about 6000 rows
and takes 5 minutes

I was able to duplicate your 5-min runtime by filling A1:Z7000 with =RAND().

While a completely different algorithm might be a good idea, I was able to
reduce that time to about 0.6 sec by applying just a couple simple principles
to your algorithm:

1. Disable certain actions that might occur as each row is hidden.

2. Hide rows from the bottom up, not from the top down.

#1 reduced the runtime to about 1.5 sec. #2 reduced the runtime to about
0.6 sec.

Of course, your times might be different. But I would expect comparable or
better improvement, considering my pervasive use of RAND.

Also note the correction, which I presume is your intention: If-Then-Else
instead of If/If.

The Range.Offset expression could be done a better way. I wrote it that way
to make it easier for you to relate to your original algorithm.

My macro....

Option Explicit

Sub doit()
Dim R As Range, i As Long
'more robust: save the original states in variant
'variables and restore them at the end
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
For i = 6210 To 7 Step -1
Set R = Range("c1").Offset(i - 1, 0)
If R.Value = "0" Then Rows(R.Row).Hidden = True _
Else If R.Value = "" Then Rows(R.Row).Hidden = True
Next
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With
End Sub

----- original message -----

"Rex" wrote:
The code shown below runs through about 6000 rows and takes 5 minutes or so
to sort the data and hide the rows. Does anyone know how to change this to
make it run faster?
Thanks for your help.

Dim R As Range
For Each R In Range("c7:c6210")
If R.Value = "0" Then Rows(R.Row).Hidden = True
If R.Value = "" Then Rows(R.Row).Hidden = True
Next

--
Rex Munn

Awesome. Thanks for your help
--
Rex Munn

"Don Guillett" wrote:

Sub hiderowsif()
Dim lr As Long
lr = Cells(Rows.Count, "c").End(xlUp).Row
Range("C1:c" & lr).AutoFilter Field:=1, _
Criteria1:="<0", Operator:=xlAnd, Criteria2:="<"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rex" wrote in message
...
The code shown below runs through about 6000 rows and takes 5 minutes or
so
to sort the data and hide the rows. Does anyone know how to change this
to
make it run faster?
Thanks for your help.

Dim R As Range
For Each R In Range("c7:c6210")
If R.Value = "0" Then Rows(R.Row).Hidden = True
If R.Value = "" Then Rows(R.Row).Hidden = True
Next

--
Rex Munn

.