please tell me what was wrong

L

lalaexcel

Hi !

I have a worksheet which holds around 4000 records. Many of these
records have zero balance and have no mean of keeping . I tried to have
a marco to get them deleted. The follow is what I tried. It is out of
my ability to understand why and how to correct. For I have to run the
marco several times before clearing all the 'zero' balance records. I
must say that it was just the result of my trial sample of about 100
records. I think most of you could imagine what the situation would be
if I had the marco run in the worksheet of 4-5 thousand . The marco is
as follow:

Sub simplify ()
Dim r as integer
Dim lastrow as long
lastrow=sheetofrecords.usedrange.rows.count
application.screenupdating=false
for r = 1 to lastrow step 1
if cells(r,3)=0 then rows(r).delete
next
end sub

Please kindly let me know how and why to correct the mistake that I
have

Regards
lalaexcel
 
R

Ron Rosenfeld

Hi !

I have a worksheet which holds around 4000 records. Many of these
records have zero balance and have no mean of keeping . I tried to have
a marco to get them deleted. The follow is what I tried. It is out of
my ability to understand why and how to correct. For I have to run the
marco several times before clearing all the 'zero' balance records. I
must say that it was just the result of my trial sample of about 100
records. I think most of you could imagine what the situation would be
if I had the marco run in the worksheet of 4-5 thousand . The marco is
as follow:

Sub simplify ()
Dim r as integer
Dim lastrow as long
lastrow=sheetofrecords.usedrange.rows.count
application.screenupdating=false
for r = 1 to lastrow step 1
if cells(r,3)=0 then rows(r).delete
next
end sub

Please kindly let me know how and why to correct the mistake that I
have

Regards
lalaexcel
Click to expand...

Your problem arises from your index getting stepped to the wrong line when you
delete a row. Instead of going from top to bottom, go from bottom to top and
you will avoid this problem.

e.g.

Change:

for r = 1 to lastrow step 1

to:

for r = lastrow to 1 step -1



--ron
 
Top