Delete Entire Row Skipping Rows

[quick_question]

Hello All,

I am trying to delete all rows with a value of "1" in column "Q" (or 17).

The issue that I am having is that the code works, kind of, as it deletes the majority of the rows, but skips some rows.

My observation is if there are say 5 rows with the following values in column Q (by row); 1, 0, 1, 1, 1.

It looks as though it's skipping rows, b/c as it deletes a row, the code is recognizing the next row as the row that was just deleted b/c it shifted up.

ie - deletes row 1, row 2 then shifts up to become row 1. If row 2 was supposed to be deleted as well (b/c it has a value of 1) the code skips this row b/c it recognizes it as row 1, which it has already addressed.

PLEASE HELP!!!!

--

Sub DeleteRows_Nexans()


Sheets("Sheet2").Activate

Dim i As Integer
Dim x As Integer

x = Sheets("Sheet2").Range("A1").Value 'Identifies # of rows of data


For i = 1 To x
If Cells(i, 17).Value = 1 Then
Rows(i).EntireRow.Delete
End If
Next i
End Sub

 

[steve the fish]

If you do things like this you need to step up rather than down. So x To 1 Step -1 rather than 1 To x

 

[njimack]

When deleting rows, you need to work backwards...

Sub DeleteRows_Nexans()
Application.ScreenUpdating = False
 Sheets("Sheet2").Activate
 Dim i As Integer
 Dim x As Integer
 x = Sheets("Sheet2").Range("A1").Value 'Identifies # of rows of data

 For i =[COLOR=#FF0000] [B]x To 1 Step -1[/B][/COLOR]
    If Cells(i, 17).Value = 1 Then Rows(i).EntireRow.Delete
 Next i
 
 Application.ScreenUpdating = True
 
 End Sub

 

[BarryL]

you need to go backwards

For i = 1 To x

needs to change for the below

For i = x To 1 Step -1

 

[quick_question]

Works Perfectly. Thank you for such quick reply's!!!!

 

[BarryL]

Glad you got it sorted.