MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Move up a cell in code

Posted by Bill Mahoney on February 15, 2002 2:03 PM

Hello, i am running through a loop where i am looking for the value zero in a cell and if it finds a zero it will delete the line. The problem is that when it deletes the line the cell below it gets the focus and then i am moving to the next cell. So the cell that recieved the focus after the delete is not being tested. How can i move back up one cell so i can test the value? The code i am using to find and delete the row is as follows:

For Each cell In Range("a1" & ":a" & Count)
If cell.Value = 0 Then

Rows(cell.row & ":" & cell.row).Select
Selection.Delete shift:=xlDown
End If
Next cell

count is a variable that is set to the last row number where it finds a populated cell.

Any help would be greatly appreciated.

Bill Mahoney
New York, NY

Posted by John Bennito on February 15, 2002 2:27 PM

Change the code to as follows and try it.
Hope it helps.

y = 0
For x = 1 To 20
y = y + 1
If Range("a" & y).Value = 0 Then
Selection.Delete shift:=xlDown
y = y - 1
End If
Next x

Posted by Bill Mahoney on February 15, 2002 2:34 PM

Thanks for the help.
Bill mahoney

Posted by Bariloche on February 15, 2002 9:53 PM

Loop backwards


When doing deletions, its best to work from the bottom up (or from right to left, for columns). In your example code, your For statement should be:

For x = 20 to 1, Step -1

Generally speaking, its a poor practice (IMO) to change a counter within a loop.

have fun