Loop hits Row 8 and goes to End Sub??

[Ed]

This workbook contains three columns - column C is an occasional "X"
inserted by another macro. A macro is supposed to run down Col C and delete
any row containing "X". It works fine, skipping past blank rows and
deleting X'ed rows - until it processes Row 8, that is.

I just stepped through this *again* looking at variable values after each
step. NowRow was 7, LastRow was 76, C was blank. The macro Offset one row
as programmed, updated NowRow to 8, went to Loop, AND THEN WENT TO END SUB!
Fourth time in a row! Can someone drop-kick me in the direction of "WHY?!?"

Ed
_______________________

Sub BBoxUpdate()

Dim NowRow As String
Dim LastRow As String

' Find end of used range
LastRow = Range("A65536").End(xlUp).Row

' Start at C1 and search down
Range("C1").Select
NowRow = ActiveCell.Row

Do While NowRow <= LastRow

' If cell has "X", delete row
If ActiveCell.Value <> "" Then
ActiveCell.EntireRow.Delete
' and update LastRow
LastRow = Range("A65536").End(xlUp).Row
Else ' Drop one row
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Select
NowRow = ActiveCell.Row ' update variable
End If

Loop

End Sub

 

[Tom Ogilvy]

Sub BBoxUpdate()

Dim NowRow As String
Dim LastRow As String

' Find end of used range
LastRow = Range("A65536").End(xlUp).Row

' Start at last row and search up

for NowRow = LastRow to 1
' If cell has "X", delete row
If cells(NowRow,3).Value <> "" Then
Cells(NowRow,3).EntireRow.Delete
End If
Next
End Sub

 

[steve]

Ed,

Try it this way:

Sub BBoxUpdate()

Dim x as Long
Dim LastRow As Long

' prevent screen flicker and make code faster
Application.ScreenUpdating = False

' Find end of used range
LastRow = Range("A65536").End(xlUp).Row

' Start at end of range and work up
For x = 1 to LastRow step -1
' If cell has "X", delete row
If LCase(Cells(x,3)) = "x" Then ' LCase to catch x or X
Rows(x).Delete
End If
Next


Application.ScreenUpdating = True
End Sub

Also you declared NowRow and LastRow as Strings. They would work
better as Long since they are numbers.

 

[Neil Eves]

You might want to change String to Long

Dim NowRow As Long
Dim LastRow As Long

Neil

 

[Tom Ogilvy]

Whoops, forgot the step -1

for NowRow = LastRow to 1
should be

for NowRow = LastRow to 1 Step -1

 

[Ed]

Thanks, Tom. I'm assuming that, since I was deleting rows that were being
counted, I was confusing the macro? Or something like that?

Ed

 

[Ed]

Thanks for the assist, Steve. I appreciate the time.

Ed

 

[Ed]

Tom, I tried it, but it wouldn't go anywhere. But I did use your idea of
going to the bottom and moving up, rather than from the top down. So I
changed my code to Offset -1, and Do Until NowRow = 1, and it works okay. I
don;t know why yours wouldn't go.

Thanks for helping. I appreciate it.

Ed

 

[Tom Ogilvy]

Because I left out the Step -1 on the end of the loop - see my correction

 

[Ed]

I made the correction and it worked great!

At first, I was wondering how NowRow would go from LastRow to 1 if NowRow
was never updated in the code somewhere (Activecell.Row). Then I read the
Help files on Step and For ... Next loops, and found where the counter is
automatically incremented by the step value. That's going to help me
greatly next time I write one of these things.

Many thanks, Tom.
Ed