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
.