Looping problem 1

[willyboy58]

Looping problem

I have some rows of data that I need to delete. If there IS NOT a customer number in column A AND column C (called StepCell) = 80, 81, or 82, that is OK.

But if there IS NOT a customer number in column A and column C is NOT equal to 80, 81, 82 then I want to delete all information in that row.

Have to open task manager to stop procedure. My procedure with REM’d notes is below.
-----------------
Sub DeleteRowWithNoCustNumber()
Dim StepCell As String

Range("A1&quot.Select
StepCell = ActiveCell.Offset(0, 2)

'start of loop
Do

With ActiveCell

If .Offset(0, 0).Formula = &quot;&quot; And StepCell <> 80 Or 81 Or 82 Then
ActiveCell.Offset(0, 0).EntireRow.Select
Selection.Delete
Else

End If

'the following line is needed for the loop. step down one row to the next cell
ActiveCell.Offset(1, 0).Select

End With

'i believe that it is working to this point but cannot 'verify because procedure will not stop running. There is only about 3600 rows of data to work with at this point. The loop does not stop running even when ActiveCell And StepCell = &quot;&quot; (next line)

Loop Until ActiveCell And StepCell = &quot;&quot;

'when finished should go back to &quot;A1&quot;
Range(&quot;A1&quot.Select

End Sub
---------------------------
What am I doing wrong? TIA. Bill

 

[sfvb]

There are a number of things wrong with your subroutine.

1) The line

StepCell = ActiveCell.Offset(0, 2)

needs to be inside the loop.

2) In the line

   If .Offset(0, 0).Formula = &quot;&quot; And StepCell <> 80 Or 81 Or 82 Then

If, StepCell = &quot;&quot; then you'll be compaing a string value to a number.

Also, you are doing a boolean OR on numbers 81 and 82.
A boolean OR on a number other than 0 will return True. So in effect, your if statement reads:

If (.Offset(0, 0).Formula = &quot;&quot; And Val(StepCell) <> 80) Or True Or True Then

3) The placement of the line:

   ActiveCell.Offset(1, 0).Select

will actually skip the line below any line that gets deleted.

4) The line:

   Loop Until ActiveCell And StepCell = ""

is not condsidering the ActiveCell at all. In effect you have:

   Loop Until True And StepCell = ""

There are ways to do this without selecting each cell.
But, using what you have already, I think this is what you're trying to accomplish:

Sub DeleteRowWithNoCustNumber()
   Dim StepCell   As String
   
   Range("A1").Select
   StepCell = ActiveCell.Offset(0, 2)

   'start of loop
   Do
      With ActiveCell
         If .Formula = &quot;&quot; And (Val(StepCell) < 80 Or Val(StepCell) > 82) Then
           ActiveCell.Offset(0, 0).EntireRow.Select
           Selection.Delete
         Else
            ActiveCell.Offset(1, 0).Select
         End If
      End With
   
      StepCell = ActiveCell.Offset(0, 2)
   Loop Until ActiveCell.Formula = &quot;&quot; And StepCell = &quot;&quot;

   'when finished should go back to &quot;A1&quot;
   Range(&quot;A1&quot;).Select
End Sub

 

[sfvb]

If StepCell = 80.25 is not OK, then you'll need to change the line:

If .Formula = &quot;&quot; And (Val(StepCell) < 80 Or Val(StepCell) > 82) Then

to

If .Formula = "" And _
(Val(StepCell) <> 80 AND Val(StepCell) <> 81 AND Val(StepCell) <> 82) Then