Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with validation deletion/modification | Excel Programming | |||
Problem with Conditional format deletion | Excel Discussion (Misc queries) | |||
Excel Problem in detail/updation, deletion | Excel Programming | |||
Excel VBA - Userform updation/deletion problem | Excel Programming | |||
Row Deletion | Excel Programming |