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.

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

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.

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.

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.

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.