View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.programming
Neil Eves[_2_] Neil Eves[_2_] is offline
external usenet poster
 
Posts: 2
Default 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