Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed Question
I have a couple of macros that sort through db output and delete
unwanted information Example: Do while selection < "" if selection 100 hours then selection.entirerow.delete else offset to next row endif loop Two questions: First, is there a faster way to do this, i.e. sort for greater than 100 and delete the results? Second, on one sheet that only has three columns this runs very fast. (28k rows in about 15 minutes) but on another sheet with about 35 columns it only does about 2 rows a second. What is causing such a vast difference in speed? Is it the number of columns deleted during the entirerow.delete line? Thanks for the help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed Question
One tip for vastly increasing speed is to put Code ------------------- Application.ScreenUpdating=False ...CODE... Application.ScreenUpdating=Tru ------------------- You should see a significant increase as nothing is being written t the screen whilst this is taking place. If it is for an end user think about adding: Code ------------------- Application.StatusBar = "Processing... ------------------- or something similar -- MartinShor Software Teste ----------------------------------------------------------------------- MartinShort's Profile: http://www.excelforum.com/member.php...fo&userid=2203 View this thread: http://www.excelforum.com/showthread.php?threadid=54866 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed Question
Generally speaking deleting rows is slow. To speed it up you are better off
to accumulate a large range object that encompasses all of the row you want to delete and then do one large delete at the end (as opposed to potentially thousands of one line deletes). Something like this... dim rngToSearch as range dim rng as range dim rngFound as range set rngToSearch = range("A2", cells(rows.count, "A")) for each rng in rngToSearch if rng.value 100 then if rngFound is nothing then set rngfound = rng else set rngFound = Union(rng, rngFound) end if end if next rng if rngFound is nothing then msgbox "Nothin to delete" else rngFound.entirerow.delete end if -- HTH... Jim Thomlinson "shelfish" wrote: I have a couple of macros that sort through db output and delete unwanted information Example: Do while selection < "" if selection 100 hours then selection.entirerow.delete else offset to next row endif loop Two questions: First, is there a faster way to do this, i.e. sort for greater than 100 and delete the results? Second, on one sheet that only has three columns this runs very fast. (28k rows in about 15 minutes) but on another sheet with about 35 columns it only does about 2 rows a second. What is causing such a vast difference in speed? Is it the number of columns deleted during the entirerow.delete line? Thanks for the help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed Question
Sub Test()
Dim iLastRow As Long Dim i As Long With Selection .Sort key1:=.Cells(1, 1), Order1:=xlDescending i = 1 Do While .Cells(i, 1).Value 100 i = i + 1 Loop .Cells(1, 1).Resize(i - 1).EntireRow.Delete End With End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "shelfish" wrote in message ups.com... I have a couple of macros that sort through db output and delete unwanted information Example: Do while selection < "" if selection 100 hours then selection.entirerow.delete else offset to next row endif loop Two questions: First, is there a faster way to do this, i.e. sort for greater than 100 and delete the results? Second, on one sheet that only has three columns this runs very fast. (28k rows in about 15 minutes) but on another sheet with about 35 columns it only does about 2 rows a second. What is causing such a vast difference in speed? Is it the number of columns deleted during the entirerow.delete line? Thanks for the help. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed Question
Three great responses. Thanks to all.
Bob, How do I make it leave my headers alone in this sort? Thanks again. Bob Phillips wrote: Sub Test() Dim iLastRow As Long Dim i As Long With Selection .Sort key1:=.Cells(1, 1), Order1:=xlDescending i = 1 Do While .Cells(i, 1).Value 100 i = i + 1 Loop .Cells(1, 1).Resize(i - 1).EntireRow.Delete End With End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "shelfish" wrote in message ups.com... I have a couple of macros that sort through db output and delete unwanted information Example: Do while selection < "" if selection 100 hours then selection.entirerow.delete else offset to next row endif loop Two questions: First, is there a faster way to do this, i.e. sort for greater than 100 and delete the results? Second, on one sheet that only has three columns this runs very fast. (28k rows in about 15 minutes) but on another sheet with about 35 columns it only does about 2 rows a second. What is causing such a vast difference in speed? Is it the number of columns deleted during the entirerow.delete line? Thanks for the help. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Speed Question
..Sort key1:=.Cells(1, 1), Order1:=xlDescending, Header:=xlYes
-- HTH... Jim Thomlinson "shelfish" wrote: Three great responses. Thanks to all. Bob, How do I make it leave my headers alone in this sort? Thanks again. Bob Phillips wrote: Sub Test() Dim iLastRow As Long Dim i As Long With Selection .Sort key1:=.Cells(1, 1), Order1:=xlDescending i = 1 Do While .Cells(i, 1).Value 100 i = i + 1 Loop .Cells(1, 1).Resize(i - 1).EntireRow.Delete End With End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "shelfish" wrote in message ups.com... I have a couple of macros that sort through db output and delete unwanted information Example: Do while selection < "" if selection 100 hours then selection.entirerow.delete else offset to next row endif loop Two questions: First, is there a faster way to do this, i.e. sort for greater than 100 and delete the results? Second, on one sheet that only has three columns this runs very fast. (28k rows in about 15 minutes) but on another sheet with about 35 columns it only does about 2 rows a second. What is causing such a vast difference in speed? Is it the number of columns deleted during the entirerow.delete line? Thanks for the help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Question on Calculation Speed | Excel Discussion (Misc queries) | |||
vlookup speed question | Excel Worksheet Functions | |||
Re-calc. speed question | Excel Worksheet Functions | |||
Question to speed up a process - | Excel Programming | |||
VBA Speed question | Excel Programming |