Problem with Deletion
 

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

Problem with Deletion
 

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


"Ben" wrote in message
...
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

Problem with Deletion
 

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

--


Gary Keramidas


"Ben" wrote in message
...
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

Problem with Deletion
 

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" wrote:

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

Problem with Deletion
 

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.

"Gary Keramidas" wrote:

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


"Ben" wrote in message
...
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

Problem with Deletion
 

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" wrote:

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" wrote:

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

Problem with Deletion
 

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.

"Ben" wrote:

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" wrote:

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" wrote:

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