Problem with Deletion

[Ben]

I am trying to delete all the 1's from column A, just those cells though, not
the whole row. I am getting error 1004 on the Selection.Delete line. It says
Range Class Failed.

Thanks

'Delete 1 Values from Date Column
x = 2
Do While Cells(x, 1).Value <> ""
If Cells(x, 1) = "1" Then
Range("A" & x).Select
Selection.Delete Shift:=x1Up
x = x + 1
Else
x = x + 1
End If
Loop

Thanks

 

[Gary Keramidas]

maybe something like this, start at the bottom and work up

Sub test()
Dim x As Long
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
'Delete 1 Values from Date Column
x = lastrow
Do While Cells(x, 1).Value <> "" And x > 1
If Cells(x, 1) = "1" Then
Range("A" & x).Delete Shift:=xlUp
x = x - 1
Else
x = x - 1
End If
Loop

End Sub

 

[Gary Keramidas]

forgot to mention, you had a 1 instead of an l, maybe just a typo getting
the code in the post.

Selection.Delete Shift:=x1Up

 

[JLGWhiz]

Selection.Delete Shift:=x1Up 'Your code
Selection.Delete Shift:=xlUp 'Corrected

Don't know if you have that in your actual code, but "1" (one) won't work
for "l" (ell). That is the first 1 you need to delete.

 

[Ben]

I fixed the error with the ell instead of the one and it helped, but still
has not fully fixed the problem. I am trying your code Gary but it keeps
calculating.

 

[Ben]

I got my code to work I just had to switch the 1 to ell and take out the x=
x+1 from after the endif.

 

[JLGWhiz]

Yes that will work using the Do ... Loop and an else condition in the If
statement. However, as Gary pointed out, if you were using a For ... Next
statement, you would want to set it to start at the bottom of the range and
work to the top or you would have the possibility of skipping rows where the
search criteria was the same in adjacent rows. That is caused by the
shifting of an unchecked row into a cell that has just been checked and the
interrogator moves one cell down for each Next execution. Just wanted to
make sure you had this information in case you decide to change your code
structure.