Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro speed
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro speed
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro speed
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro speed
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro speed
"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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro speed
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro speed
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help to speed up this macro | Excel Programming | |||
VBA macro speed | Excel Programming | |||
can anybody speed up this macro | Excel Programming | |||
Help, need to speed up this macro | Excel Discussion (Misc queries) | |||
Speed-up a macro! | Excel Programming |