Simple Loop command

**chrismann85** · 03-24-2011, 06:31 AM

Hi All,

Really simple question here. I'm trying to delete a row if the value in the Column 62 is 0.

Here's what I have so far:

Sub findlast300()
Dim row As Integer
row = 3
For i = 1 To 9000
If Cells(row, 62) = 0 Then

Rows(row).Select
Selection.Delete Shift:=x1Up
Else: row = row + 1
End If
i = i + 1
Next

End Sub

It runs ok and deletes the row but doesn't seem to recognise when the value in column 62 (BJ) is equal to 1.

can anyone help?

Thanks

**TMS** · 03-24-2011, 06:55 AM

When deleting rows, always start from the bottom.

Try this:

Sub findlast300_v2()
Dim lRC As Long ' row counter
Application.ScreenUpdating = False
For lRC = 9000 To 3 Step -1
    If Cells(lRC, 62) = 0 Then
        Rows(lRC).Delete Shift:=x1Up
    End If
Next
Application.ScreenUpdating = True
End Sub

Regards

**abousetta** · 03-24-2011, 06:59 AM

Hi,

This seems to work for me, unless I don't understand the problem:

Option Explicit

Sub findlast300()
Dim row As Integer
Dim i As Integer

row = 3
For i = 1 To 9000
If Cells(row, 62) = 0 Then

Rows(row).Delete Shift:=xlUp
Else: row = row + 1
End If
i = i + 1
Next

End Sub

abousetta

**abousetta** · 03-24-2011, 07:02 AM

Also you might want to limit the number of rows unless you always have 9000 rows of data. This seems to be slowing down the macro.

In general, TMShucks approach is more efficient.

abousetta