slow code needs tuning
 

Greetings and TIA for your time
I have a database of about 2,500 records.
I am deleting all records that have a null string (="") in field1:

with range("Field1")
for each cl in .cells
if cl = "" then cl.entirerow.delete
next
end with

This works fine but is slow.

if the cells i were looking for contained blanks, I could use:
range("Field1").specialcells(xlblanks).entirerow.d elete
which I think would be much quicker. sadly, I'm hunting for null strngs.
Any ideas on how to speed the code up?
--
David

slow code needs tuning
 

just wondering if it's the screen updating that's a problem, too.

have you tried

Application.ScreenUpdating = False

at the beginning of the code and

Application.ScreenUpdating = True

at the end of the code


just a guess
--


Gary


"David" wrote in message
...
Greetings and TIA for your time
I have a database of about 2,500 records.
I am deleting all records that have a null string (="") in field1:

with range("Field1")
for each cl in .cells
if cl = "" then cl.entirerow.delete
next
end with

This works fine but is slow.

if the cells i were looking for contained blanks, I could use:
range("Field1").specialcells(xlblanks).entirerow.d elete
which I think would be much quicker. sadly, I'm hunting for null strngs.
Any ideas on how to speed the code up?
--
David

slow code needs tuning
 

Try the following. The only thing I did not account for is if the first
row of the range contains a blank.

Option Explicit

Sub Test()

Dim rFind As Range
Dim rTemp As Range

Set rFind = Range("Test").Find(what:="", lookat:=xlWhole)
While Not rFind Is Nothing
' Back up one row
Set rTemp = rFind.Offset(-1, 0)
rFind.EntireRow.Delete
Set rFind = Range("Test").FindNext(after:=rTemp)
Wend

End Sub



*** Sent via Developersdex http://www.developersdex.com ***

slow code needs tuning
 

"David" wrote in message
...
Greetings and TIA for your time
I have a database of about 2,500 records.
I am deleting all records that have a null string (="") in field1:

with range("Field1")
for each cl in .cells
if cl = "" then cl.entirerow.delete
next
end with

This works fine but is slow.

if the cells i were looking for contained blanks, I could use:
range("Field1").specialcells(xlblanks).entirerow.d elete
which I think would be much quicker. sadly, I'm hunting for null strngs.
Any ideas on how to speed the code up?
--
David

Untested; but autofiltering(Field1)=VbNull deleting the resulting "visibile"
rows should speed things up??

With Range("Field1")
..AutoFilter Field:=1, Criteria1:=vbnull
..SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With

slow code needs tuning
 

Try:
Application.ScreenUpdating = False
with range("Field1")
for each cl in .cells
if cl = "" then cl.entirerow.delete
next
end with
Application.ScreenUpdating = True





"David" wrote:

Greetings and TIA for your time
I have a database of about 2,500 records.
I am deleting all records that have a null string (="") in field1:

with range("Field1")
for each cl in .cells
if cl = "" then cl.entirerow.delete
next
end with

This works fine but is slow.

if the cells i were looking for contained blanks, I could use:
range("Field1").specialcells(xlblanks).entirerow.d elete
which I think would be much quicker. sadly, I'm hunting for null strngs.
Any ideas on how to speed the code up?
--
David

slow code needs tuning
 

Instead of using "if cl = ""...", use "if len(c)=0 ...", it is a numerical
evaluation, which is runs faster than a string evaluation. Also, turn screen
updating off:

application.screenupdating = False

'do loop here

application.screenupdating = True

Be careful about using the word "null." "Null" is a legitimate value in VB
(vbNull), whereas a zero-length string is NOT a null value.


"David" wrote:

Greetings and TIA for your time
I have a database of about 2,500 records.
I am deleting all records that have a null string (="") in field1:

with range("Field1")
for each cl in .cells
if cl = "" then cl.entirerow.delete
next
end with

This works fine but is slow.

if the cells i were looking for contained blanks, I could use:
range("Field1").specialcells(xlblanks).entirerow.d elete
which I think would be much quicker. sadly, I'm hunting for null strngs.
Any ideas on how to speed the code up?
--
David