making macro run faster?

  • Thread starter Thread starter goorooj
  • Start date Start date
G

goorooj

hi,

currently i´m using this loop

x = 1

While Cells(x, 1) <> "Endlog"
If Cells(x, 4) = "INFORMATION" Then
Range("A" & x & ":K" & x).Select
Range("A" & x & ":K" & x).Delete
x = x - 1
End If

x = x + 1

Wend

in a macro to delete all rows from a sheet that contain the ter
information in column 4, it´s for log file viewing.

endlog is the last word in the sheet.
it works fine, but:

deleting up to 30.000 rows in a sheet this way takes quite a long time
up to half an hour... is there any way to make the task run faster?
with a do...loop or any other, faster working loop
 
Ues a cells.find to find the information then instead of select the
delete at the end of the
cells.find (what:="your term").entirerow.delete

That will stop the program from looking at every row.

Keith
www.kjtfs.co
 
A couple of other options to try.

Rather than do a While loop testing for a string (very inefficient), why not
do a Find on that string, and get the row number of the found cell, and do a
simple For ... Next loop, like so

For x = cLastRow To 1 Step -1
'...
Next x

where cLastRow is a Long variable holding the found cell row. Also removes
the x increment step, VBA does it implicitly.

Secondly, rather than delete as you go, build up a simple range union of
matched cells, and delete them at the end. For example

For x = cLastRow To 1 Step -1
If Cells(x, 4) = "INFORMATION" Then
If oRng Is Nothing Then
Set oRng = Range("A" & x & ":K" & x)
Else
Set oRng = Union( oRng, Range("A" & x & ":K" & x))
End If End If
Next x
If Not oRng Is Nothing Then oRng.Delete

There is also more built-in error handling in these techniques. It may not
be faster, but worth a try.

Oh, and turn ScreenUpdating off.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
If you will be deleting less than 8192 rows, you can use something like this

Sub DeleteRows
Dim rng as Range
Columns("D:D").Replace What:="INFORMATION", _
Replacement:="=NA()", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
set rng = Columns(4).SpecialCells(xlCellTypeFormulas, 16)
rng.EntireRow.Delete
End Sub

If it would be more than 8192 rows, then I would still use this approach,
but do it in 2 or 3 sections (each of less than 8192 rows)
 
Consider adapting the suggestions in http://groups.google.com/groups?
selm=MPG.15e9939057ed7a1998a1e9%40msnews.microsoft.com

Though, with the added knowledge of 2-1/2 years after that post, I
probably wouldn't use option #3 for a very large data set. The Union
method can become painfully slow.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
You don't need to select or delete.
Can you not sort it?
If you insert a column and use a formula to find your string.

[YourRange].formula = "=IF(D2="INFORMATION",0,"ZZ")

YourRange is the range of the column adjacent to your data.
Sort with this column then delete or Clear (preferably) the rows with 0.
If you need a macro just record the process.
Or have a look at this one. (Here I assumed you have headers in
A1 to F1, and the formula column is the 6th one)


Sub DellInfo()
[F2:F30000].Formula = "=IF(D2=""INFORMATION"",0,""ZZ"")"
[A1:F30000].Sort key1:=[F2], order1:=xlAscending, header:=xlGuess
[A2].Resize(Application.Count([F2:F30000]), 6).Delete shift:=xlUp
End Sub


This will take about a second on a slow machine.
To delete the formula column via code
Columns(6).clear
I left it in to see the results, or for you to delete it manually.


Regards Robert
 
Back
Top