Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT LOOP ?
I have a sheet that is Subtotaled by Receipts #'s I'm trying to delete
Receipts that are less then 5 Please help Sub deleterowtest() Dim rngColB As Range Dim ipointer As Long Dim sSting As String sSting = "Total" 'Change "B" to the column your data in in you are looking to find Set rngColB = ActiveSheet.Range(Cells(1, "B"), Cells(Rows.Count, "B").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rngColB For ipointer = .Rows.Count To 1 Step -1 If Right(.Cells(ipointer).Value, 5) = (sSting) And _ .Cells(ipointer).Offset(0, 1).Value < 5 Then MsgBox .Cells(ipointer).Offset(0, 1).Value '.Cells(ipointer).EntireRow.Delete End If Next ipointer End With End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT LOOP ?
Don My data looks like this I would like to delete receipt's where the
subtotal is less then 5 26-Sep-07 504326 4 'Delete 504326 Total 4 'Delete 27-Sep-07 504400 2 'Keep 27-Sep-07 504400 2 'Keep 27-Sep-07 504400 6 'Keep 27-Sep-07 504400 2 'Keep 504400 Total 12'Keep 28-Sep-07 504750 3 'Delete 504750 Total 3 'Delete 29-Sep-07 504823 4 'Delete 504823 Total 4'Delete 30-Sep-07 504983 4 'Delete 504983 Total 4'Delete "Don Guillett" wrote: something simpler for i=cells(rows.count,"b").end(xlup).row to 1 step -1 'one line if ucase(cells(i,5))="TOTAL" and cells(i,6)<5 then rows(i).delete next i -- Don Guillett Microsoft MVP Excel SalesAid Software "Mike" wrote in message ... I have a sheet that is Subtotaled by Receipts #'s I'm trying to delete Receipts that are less then 5 Please help Sub deleterowtest() Dim rngColB As Range Dim ipointer As Long Dim sSting As String sSting = "Total" 'Change "B" to the column your data in in you are looking to find Set rngColB = ActiveSheet.Range(Cells(1, "B"), Cells(Rows.Count, "B").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rngColB For ipointer = .Rows.Count To 1 Step -1 If Right(.Cells(ipointer).Value, 5) = (sSting) And _ .Cells(ipointer).Offset(0, 1).Value < 5 Then MsgBox .Cells(ipointer).Offset(0, 1).Value '.Cells(ipointer).EntireRow.Delete End If Next ipointer End With End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT LOOP ?
Mike,
What do you think is wrong with this. It reads up the used range of columnn B and if the last 5 characters are "Total" and the value in the corresponding row of column C is less than 5 the entirerow is deleted assuming of course the delete line is un-commented. Is that what you expect to happen? Mike "Mike" wrote: I have a sheet that is Subtotaled by Receipts #'s I'm trying to delete Receipts that are less then 5 Please help Sub deleterowtest() Dim rngColB As Range Dim ipointer As Long Dim sSting As String sSting = "Total" 'Change "B" to the column your data in in you are looking to find Set rngColB = ActiveSheet.Range(Cells(1, "B"), Cells(Rows.Count, "B").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rngColB For ipointer = .Rows.Count To 1 Step -1 If Right(.Cells(ipointer).Value, 5) = (sSting) And _ .Cells(ipointer).Offset(0, 1).Value < 5 Then MsgBox .Cells(ipointer).Offset(0, 1).Value '.Cells(ipointer).EntireRow.Delete End If Next ipointer End With End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT LOOP ?
I would like to Delete the Receipt number and the subtotal for the receipt
number "Mike H" wrote: Mike, What do you think is wrong with this. It reads up the used range of columnn B and if the last 5 characters are "Total" and the value in the corresponding row of column C is less than 5 the entirerow is deleted assuming of course the delete line is un-commented. Is that what you expect to happen? Mike "Mike" wrote: I have a sheet that is Subtotaled by Receipts #'s I'm trying to delete Receipts that are less then 5 Please help Sub deleterowtest() Dim rngColB As Range Dim ipointer As Long Dim sSting As String sSting = "Total" 'Change "B" to the column your data in in you are looking to find Set rngColB = ActiveSheet.Range(Cells(1, "B"), Cells(Rows.Count, "B").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rngColB For ipointer = .Rows.Count To 1 Step -1 If Right(.Cells(ipointer).Value, 5) = (sSting) And _ .Cells(ipointer).Offset(0, 1).Value < 5 Then MsgBox .Cells(ipointer).Offset(0, 1).Value '.Cells(ipointer).EntireRow.Delete End If Next ipointer End With End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT LOOP ?
Try this
for i=cells(rows.count,"b").end(xlup).row to 1 step -1 'one line if right(cells(i,2,5)="Total" and cells(i,3)<5 then rows(i).delete next i -- Don Guillett Microsoft MVP Excel SalesAid Software "Mike" wrote in message ... Don My data looks like this I would like to delete receipt's where the subtotal is less then 5 26-Sep-07 504326 4 'Delete 504326 Total 4 'Delete 27-Sep-07 504400 2 'Keep 27-Sep-07 504400 2 'Keep 27-Sep-07 504400 6 'Keep 27-Sep-07 504400 2 'Keep 504400 Total 12'Keep 28-Sep-07 504750 3 'Delete 504750 Total 3 'Delete 29-Sep-07 504823 4 'Delete 504823 Total 4'Delete 30-Sep-07 504983 4 'Delete 504983 Total 4'Delete "Don Guillett" wrote: something simpler for i=cells(rows.count,"b").end(xlup).row to 1 step -1 'one line if ucase(cells(i,5))="TOTAL" and cells(i,6)<5 then rows(i).delete next i -- Don Guillett Microsoft MVP Excel SalesAid Software "Mike" wrote in message ... I have a sheet that is Subtotaled by Receipts #'s I'm trying to delete Receipts that are less then 5 Please help Sub deleterowtest() Dim rngColB As Range Dim ipointer As Long Dim sSting As String sSting = "Total" 'Change "B" to the column your data in in you are looking to find Set rngColB = ActiveSheet.Range(Cells(1, "B"), Cells(Rows.Count, "B").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rngColB For ipointer = .Rows.Count To 1 Step -1 If Right(.Cells(ipointer).Value, 5) = (sSting) And _ .Cells(ipointer).Offset(0, 1).Value < 5 Then MsgBox .Cells(ipointer).Offset(0, 1).Value '.Cells(ipointer).EntireRow.Delete End If Next ipointer End With End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT LOOP ?
Mike H My data looks like this I would like to delete receipt's where the
subtotal is less then 5 26-Sep-07 504326 4 'Delete 504326 Total 4 'Delete 27-Sep-07 504400 2 'Keep 27-Sep-07 504400 2 'Keep 27-Sep-07 504400 6 'Keep 27-Sep-07 504400 2 'Keep 504400 Total 12'Keep 28-Sep-07 504750 3 'Delete 504750 Total 3 'Delete 29-Sep-07 504823 4 'Delete 504823 Total 4'Delete 30-Sep-07 504983 4 'Delete 504983 Total 4'Delete "Mike H" wrote: Mike, What do you think is wrong with this. It reads up the used range of columnn B and if the last 5 characters are "Total" and the value in the corresponding row of column C is less than 5 the entirerow is deleted assuming of course the delete line is un-commented. Is that what you expect to happen? Mike "Mike" wrote: I have a sheet that is Subtotaled by Receipts #'s I'm trying to delete Receipts that are less then 5 Please help Sub deleterowtest() Dim rngColB As Range Dim ipointer As Long Dim sSting As String sSting = "Total" 'Change "B" to the column your data in in you are looking to find Set rngColB = ActiveSheet.Range(Cells(1, "B"), Cells(Rows.Count, "B").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rngColB For ipointer = .Rows.Count To 1 Step -1 If Right(.Cells(ipointer).Value, 5) = (sSting) And _ .Cells(ipointer).Offset(0, 1).Value < 5 Then MsgBox .Cells(ipointer).Offset(0, 1).Value '.Cells(ipointer).EntireRow.Delete End If Next ipointer End With End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT LOOP ?
Dosen't work
"Don Guillett" wrote: Try this for i=cells(rows.count,"b").end(xlup).row to 1 step -1 'one line if right(cells(i,2,5)="Total" and cells(i,3)<5 then rows(i).delete next i -- Don Guillett Microsoft MVP Excel SalesAid Software "Mike" wrote in message ... Don My data looks like this I would like to delete receipt's where the subtotal is less then 5 26-Sep-07 504326 4 'Delete 504326 Total 4 'Delete 27-Sep-07 504400 2 'Keep 27-Sep-07 504400 2 'Keep 27-Sep-07 504400 6 'Keep 27-Sep-07 504400 2 'Keep 504400 Total 12'Keep 28-Sep-07 504750 3 'Delete 504750 Total 3 'Delete 29-Sep-07 504823 4 'Delete 504823 Total 4'Delete 30-Sep-07 504983 4 'Delete 504983 Total 4'Delete "Don Guillett" wrote: something simpler for i=cells(rows.count,"b").end(xlup).row to 1 step -1 'one line if ucase(cells(i,5))="TOTAL" and cells(i,6)<5 then rows(i).delete next i -- Don Guillett Microsoft MVP Excel SalesAid Software "Mike" wrote in message ... I have a sheet that is Subtotaled by Receipts #'s I'm trying to delete Receipts that are less then 5 Please help Sub deleterowtest() Dim rngColB As Range Dim ipointer As Long Dim sSting As String sSting = "Total" 'Change "B" to the column your data in in you are looking to find Set rngColB = ActiveSheet.Range(Cells(1, "B"), Cells(Rows.Count, "B").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rngColB For ipointer = .Rows.Count To 1 Step -1 If Right(.Cells(ipointer).Value, 5) = (sSting) And _ .Cells(ipointer).Offset(0, 1).Value < 5 Then MsgBox .Cells(ipointer).Offset(0, 1).Value '.Cells(ipointer).EntireRow.Delete End If Next ipointer End With End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT LOOP ?
Send me a workbook to the address below -- Don Guillett Microsoft MVP Excel SalesAid Software "Mike" wrote in message ... Dosen't work "Don Guillett" wrote: Try this for i=cells(rows.count,"b").end(xlup).row to 1 step -1 'one line if right(cells(i,2,5)="Total" and cells(i,3)<5 then rows(i).delete next i -- Don Guillett Microsoft MVP Excel SalesAid Software "Mike" wrote in message ... Don My data looks like this I would like to delete receipt's where the subtotal is less then 5 26-Sep-07 504326 4 'Delete 504326 Total 4 'Delete 27-Sep-07 504400 2 'Keep 27-Sep-07 504400 2 'Keep 27-Sep-07 504400 6 'Keep 27-Sep-07 504400 2 'Keep 504400 Total 12'Keep 28-Sep-07 504750 3 'Delete 504750 Total 3 'Delete 29-Sep-07 504823 4 'Delete 504823 Total 4'Delete 30-Sep-07 504983 4 'Delete 504983 Total 4'Delete "Don Guillett" wrote: something simpler for i=cells(rows.count,"b").end(xlup).row to 1 step -1 'one line if ucase(cells(i,5))="TOTAL" and cells(i,6)<5 then rows(i).delete next i -- Don Guillett Microsoft MVP Excel SalesAid Software "Mike" wrote in message ... I have a sheet that is Subtotaled by Receipts #'s I'm trying to delete Receipts that are less then 5 Please help Sub deleterowtest() Dim rngColB As Range Dim ipointer As Long Dim sSting As String sSting = "Total" 'Change "B" to the column your data in in you are looking to find Set rngColB = ActiveSheet.Range(Cells(1, "B"), Cells(Rows.Count, "B").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rngColB For ipointer = .Rows.Count To 1 Step -1 If Right(.Cells(ipointer).Value, 5) = (sSting) And _ .Cells(ipointer).Offset(0, 1).Value < 5 Then MsgBox .Cells(ipointer).Offset(0, 1).Value '.Cells(ipointer).EntireRow.Delete End If Next ipointer End With End Sub |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT LOOP ?
I had a missing ) but you don't have any that meet the criteria. Try msbox
with =5 to see. Sub deleterowsif() Dim i As Long For i = Cells(Rows.Count, "b").End(xlUp).Row To 1 Step -1 'one line 'If Right(Cells(i, 2), 5) = "Total" And Cells(i, 3) < 5 Then Rows(i).Delete If Right(Cells(i, 2), 5) = "Total" And Cells(i, 3) = 5 Then MsgBox i Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Mike" wrote in message ... Mike H My data looks like this I would like to delete receipt's where the subtotal is less then 5 26-Sep-07 504326 4 'Delete 504326 Total 4 'Delete 27-Sep-07 504400 2 'Keep 27-Sep-07 504400 2 'Keep 27-Sep-07 504400 6 'Keep 27-Sep-07 504400 2 'Keep 504400 Total 12'Keep 28-Sep-07 504750 3 'Delete 504750 Total 3 'Delete 29-Sep-07 504823 4 'Delete 504823 Total 4'Delete 30-Sep-07 504983 4 'Delete 504983 Total 4'Delete "Mike H" wrote: Mike, What do you think is wrong with this. It reads up the used range of columnn B and if the last 5 characters are "Total" and the value in the corresponding row of column C is less than 5 the entirerow is deleted assuming of course the delete line is un-commented. Is that what you expect to happen? Mike "Mike" wrote: I have a sheet that is Subtotaled by Receipts #'s I'm trying to delete Receipts that are less then 5 Please help Sub deleterowtest() Dim rngColB As Range Dim ipointer As Long Dim sSting As String sSting = "Total" 'Change "B" to the column your data in in you are looking to find Set rngColB = ActiveSheet.Range(Cells(1, "B"), Cells(Rows.Count, "B").End(xlUp)) 'Work backwards from bottom to top when deleting rows With rngColB For ipointer = .Rows.Count To 1 Step -1 If Right(.Cells(ipointer).Value, 5) = (sSting) And _ .Cells(ipointer).Offset(0, 1).Value < 5 Then MsgBox .Cells(ipointer).Offset(0, 1).Value '.Cells(ipointer).EntireRow.Delete End If Next ipointer End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IME MODE FOR EXCEL 2007 (URGENT URGENT) | Excel Discussion (Misc queries) | |||
URGENT- Can't get out of infinite loop | Excel Discussion (Misc queries) | |||
Urgent-Urgent VBA LOOP | Excel Discussion (Misc queries) | |||
Urgent Help needed on! Copy and Paste a Formula as Values using a Loop statement | Excel Programming | |||
Macro help urgent urgent | Excel Programming |