Loop hits Row 8 and goes to End Sub??
 

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

Loop hits Row 8 and goes to End Sub??
 

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

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
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

Loop hits Row 8 and goes to End Sub??
 

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.
--
sb
"Ed" wrote in message
...
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

Loop hits Row 8 and goes to End Sub??
 

You might want to change String to Long

Dim NowRow As Long
Dim LastRow As Long

Neil

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
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

Loop hits Row 8 and goes to End Sub??
 

Whoops, forgot the step -1

for NowRow = LastRow to 1
should be

for NowRow = LastRow to 1 Step -1

--
Regards,
Tom Ogilvy

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
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

Loop hits Row 8 and goes to End Sub??
 

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

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
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

Loop hits Row 8 and goes to End Sub??
 

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

Ed

"steve" wrote in message
...
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.
--
sb
"Ed" wrote in message
...
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

Loop hits Row 8 and goes to End Sub??
 

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" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
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

Loop hits Row 8 and goes to End Sub??
 

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

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
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" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
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

Loop hits Row 8 and goes to End Sub??
 

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

"Tom Ogilvy" wrote in message
...
Because I left out the Step -1 on the end of the loop - see my
correction

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
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" wrote in message
...
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

--
Regards,
Tom Ogilvy

"Ed" wrote in message
...
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