PDA

View Full Version : Stop skipping rows!!



lijon
07-24-2008, 04:03 AM
Hi Folks, I'm dying here over something simple.
I want the bold part of the script below to delete entire rows when cell c_ is less than 3million. It does, but then restarts the checking process at the next line down, instead of the current line.
Using Selection.Delete Shift:=xlUp doesn't seem to fix it either.
MUCHO GRACIAS for any and all assistance.


Sub Get()

' Macro recorded 7/21/2008 by Lijon

Dim count As Integer
Dim cnt As Integer
Dim slash As Integer

Range("a1").Select
If Range("a1").Offset(1, 0).Value <> "" Then
Selection.End(xlDown).Select
End If
erow = ActiveCell.Row
srow = 2

Range("a1").Select
For cnt = srow To erow
If Range("c" & cnt).Value < 3000000 Then Range("c" & cnt).EntireRow.Delete
Next cnt

End Sub

Edited by Aussiebear (Wrapped your code with the vba tags)

Bob Phillips
07-24-2008, 06:28 AM
For cnt = erow To srow Step -1
If Range("c" & cnt).Value < 3000000 Then
Rows(cnt).Delete
End If
Next cnt

IrishCharm
07-24-2008, 07:27 AM
This isn't as technical as above but if you insert activecell.offset(-1,0) after the cell delete bit in the loop it will bring it back up to the previous "clean" cell so when you move on you wont skip any cells.

mdmackillop
07-24-2008, 02:03 PM
Hi Sarah,
Looping is inherently slow. Activating each cell in the loop is slower, and backstepping on every found cell will be worse still. There is a "right" way to do it, so I would stick with that.
Regards
MD