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.
Regards,
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
Rows(y).Select
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
Bill,
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
