Stop a loop when certain data is found
 

I have inherited this code from someone else. I want the loop to stop when it
comes to something other than "PO De-Expedite". I have tried several
different things w/o success. The code has to churn through a couple of
thousand rows of data and it is extremely slow so I inserted a sort which
puts all potentially deletable (is that a word?) rows at top.



Dim LastCell
Dim C_LastCell
Dim NumberVal As Long
Dim temp
Dim x As Long
Dim Delete_Flag As Boolean
Dim RightNow As Date

x = 2
RightNow = Date

' Find the last populated cell in the 'A' column and setup for all other
columns

Range("A1").Select
LastCell =
ActiveCell.SpecialCells(xlLastCell).Address(RowAbs olute:=False, _

ColumnAbsolute:=False)
NumberVal = Right(LastCell, (Len(LastCell) - 1))
C_LastCell = "C" & NumberVal

' Delete all PO De-Expedite rows where the Dock Date is more than 56 days
out.

For x = 2 To NumberVal
Delete_Flag = False
Range("M" & x).Select

If Left(ActiveCell.Value, 14) = "PO De-Expedite" Then
Range("L" & x).Select
If ActiveCell.Value RightNow + 56 Then
Rows(x & ":" & x).Select
Selection.Delete Shift:=xlUp
x = x - 1
End If
End If
Next x
--
Help greatly appreciated - Jeannell

Stop a loop when certain data is found
 

Change your If statement.

For x = 2 To NumberVal
Delete_Flag = False
Range("M" & x).Select
If Left(ActiveCell.Value, 14) = "PO De-Expedite" _
Then
Range("L" & x).Select
If ActiveCell.Value RightNow + 56 _
Then
Rows(x & ":" & x).Select
Selection.Delete Shift:=xlUp
x = x - 1
Else
End If
Else
Exit For
End If
Next x


HTH,
Paul


--

"jsmith" wrote in message
...
I have inherited this code from someone else. I want the loop to stop when
it
comes to something other than "PO De-Expedite". I have tried several
different things w/o success. The code has to churn through a couple of
thousand rows of data and it is extremely slow so I inserted a sort which
puts all potentially deletable (is that a word?) rows at top.



Dim LastCell
Dim C_LastCell
Dim NumberVal As Long
Dim temp
Dim x As Long
Dim Delete_Flag As Boolean
Dim RightNow As Date

x = 2
RightNow = Date

' Find the last populated cell in the 'A' column and setup for all other
columns

Range("A1").Select
LastCell =
ActiveCell.SpecialCells(xlLastCell).Address(RowAbs olute:=False, _

ColumnAbsolute:=False)
NumberVal = Right(LastCell, (Len(LastCell) - 1))
C_LastCell = "C" & NumberVal

' Delete all PO De-Expedite rows where the Dock Date is more than 56
days
out.

For x = 2 To NumberVal
Delete_Flag = False
Range("M" & x).Select

If Left(ActiveCell.Value, 14) = "PO De-Expedite" Then
Range("L" & x).Select
If ActiveCell.Value RightNow + 56 Then
Rows(x & ":" & x).Select
Selection.Delete Shift:=xlUp
x = x - 1
End If
End If
Next x
--
Help greatly appreciated - Jeannell

Stop a loop when certain data is found
 

Many thanks Paul - That worked perfectly!
--
Jeannell


"PCLIVE" wrote:

Change your If statement.

For x = 2 To NumberVal
Delete_Flag = False
Range("M" & x).Select
If Left(ActiveCell.Value, 14) = "PO De-Expedite" _
Then
Range("L" & x).Select
If ActiveCell.Value RightNow + 56 _
Then
Rows(x & ":" & x).Select
Selection.Delete Shift:=xlUp
x = x - 1
Else
End If
Else
Exit For
End If
Next x


HTH,
Paul


--

"jsmith" wrote in message
...
I have inherited this code from someone else. I want the loop to stop when
it
comes to something other than "PO De-Expedite". I have tried several
different things w/o success. The code has to churn through a couple of
thousand rows of data and it is extremely slow so I inserted a sort which
puts all potentially deletable (is that a word?) rows at top.



Dim LastCell
Dim C_LastCell
Dim NumberVal As Long
Dim temp
Dim x As Long
Dim Delete_Flag As Boolean
Dim RightNow As Date

x = 2
RightNow = Date

' Find the last populated cell in the 'A' column and setup for all other
columns

Range("A1").Select
LastCell =
ActiveCell.SpecialCells(xlLastCell).Address(RowAbs olute:=False, _

ColumnAbsolute:=False)
NumberVal = Right(LastCell, (Len(LastCell) - 1))
C_LastCell = "C" & NumberVal

' Delete all PO De-Expedite rows where the Dock Date is more than 56
days
out.

For x = 2 To NumberVal
Delete_Flag = False
Range("M" & x).Select

If Left(ActiveCell.Value, 14) = "PO De-Expedite" Then
Range("L" & x).Select
If ActiveCell.Value RightNow + 56 Then
Rows(x & ":" & x).Select
Selection.Delete Shift:=xlUp
x = x - 1
End If
End If
Next x
--
Help greatly appreciated - Jeannell

Stop a loop when certain data is found
 

You're welcome.

--

"jsmith" wrote in message
...
Many thanks Paul - That worked perfectly!
--
Jeannell


"PCLIVE" wrote:

Change your If statement.

For x = 2 To NumberVal
Delete_Flag = False
Range("M" & x).Select
If Left(ActiveCell.Value, 14) = "PO De-Expedite" _
Then
Range("L" & x).Select
If ActiveCell.Value RightNow + 56 _
Then
Rows(x & ":" & x).Select
Selection.Delete Shift:=xlUp
x = x - 1
Else
End If
Else
Exit For
End If
Next x


HTH,
Paul


--

"jsmith" wrote in message
...
I have inherited this code from someone else. I want the loop to stop
when
it
comes to something other than "PO De-Expedite". I have tried several
different things w/o success. The code has to churn through a couple of
thousand rows of data and it is extremely slow so I inserted a sort
which
puts all potentially deletable (is that a word?) rows at top.



Dim LastCell
Dim C_LastCell
Dim NumberVal As Long
Dim temp
Dim x As Long
Dim Delete_Flag As Boolean
Dim RightNow As Date

x = 2
RightNow = Date

' Find the last populated cell in the 'A' column and setup for all
other
columns

Range("A1").Select
LastCell =
ActiveCell.SpecialCells(xlLastCell).Address(RowAbs olute:=False, _

ColumnAbsolute:=False)
NumberVal = Right(LastCell, (Len(LastCell) - 1))
C_LastCell = "C" & NumberVal

' Delete all PO De-Expedite rows where the Dock Date is more than 56
days
out.

For x = 2 To NumberVal
Delete_Flag = False
Range("M" & x).Select

If Left(ActiveCell.Value, 14) = "PO De-Expedite" Then
Range("L" & x).Select
If ActiveCell.Value RightNow + 56 Then
Rows(x & ":" & x).Select
Selection.Delete Shift:=xlUp
x = x - 1
End If
End If
Next x
--
Help greatly appreciated - Jeannell