# Delete Entire Row Skipping Rows

#### quick_question

##### New Member
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.

--

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

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
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

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

Code:
``````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``````

Last edited:
you need to go backwards

Code:
``For i = 1 To x``

needs to change for the below

Code:
``For i = x To 1 Step -1``

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

Last edited:

Replies
1
Views
115
Replies
5
Views
115
Replies
2
Views
63
Replies
1
Views
88
Replies
12
Views
299

1,196,188
Messages
6,013,931
Members
441,796
Latest member
kmag

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back