ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   URGENT LOOP ? (https://www.excelbanter.com/excel-programming/399631-urgent-loop.html)

Mike
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

Don Guillett
URGENT LOOP ?
 
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

Mike
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

Mike H
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

Mike
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

Don Guillett
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

Mike
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

Mike
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

Don Guillett
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

Don Guillett
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


All times are GMT +1. The time now is 10:26 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com