Macro speed

[Rex]

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

 

[Don Guillett]

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

 

[Chip Pearson]

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

 

[ryguy7272]

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.

 

[Joe User]

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]

Thanks to all you guys for your help.
Problem solved.

 

[Rex]

Awesome. Thanks for your help
--
Rex Munn

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
(e-mail address removed)


.