making macro run faster?
 

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

--
Message posted from http://www.ExcelForum.com

making macro run faster?
 

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

--
Message posted from http://www.ExcelForum.com

making macro run faster?
 

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)

"KJTFS " wrote in message
...
Ues a cells.find to find the information then instead of select then
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.com


---
Message posted from http://www.ExcelForum.com/

making macro run faster?
 

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)

--
Regards,
Tom Ogilvy


"goorooj " wrote in message
...
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 term
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?


---
Message posted from http://www.ExcelForum.com/

making macro run faster?
 

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

In article , goorooj
says...
hi,

currently i=3Fm 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 term
information in column 4, it=3Fs 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?


---
Message posted from http://www.ExcelForum.com/

making macro run faster?
 

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

"goorooj " wrote in message ...
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 term
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?


---
Message posted from http://www.ExcelForum.com/



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.590 / Virus Database: 373 - Release Date: 17/02/2004