Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
help w/ fine tuning | New Users to Excel | |||
SLOW Code... | Excel Programming | |||
Slow Code | Excel Programming | |||
Is this slow code? | Excel Programming | |||
Tuning Off Toolbars | Excel Programming |