Loop Stops before reaching EOF

[RSJohnson]

My Do loop will stop at a row as it is iterating through an Excel worksheet for no apparent reason. There is nothing unusual about the row where processing stops. If I delete the row and restart the processing the code will go pass the point where it stopped. It will eventually reach another point where it stops again for not apparent reason.

I believe I was once told that this has to do with blanks that are not visible but I don’t remember what the fix was. Am I remembering correctly and if so can someone share a solution?

 

[TonyJollans]

Hi semaphore,

Does it involve deleting rows? Does it stop with an error message? Any chance of seeing the code?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[RSJohnson]

It does not involve deleting rows. There is no error message.

Here' my loop code;

intStop = Sheets("First QT").Range(mstrGMStart).CurrentRegion.Rows.Count 'Change to find l

Do Until intCounter = intStop
If rngDAFRPCA = rngGMPCA Then

'ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("First QT").Select
rngToCopy.Select
Selection.Copy
Sheets("Prepared_Expense").Select
'Range(rngDestin).Select 'This caused the error Range is redundent
rngDestin.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

'Moves to next row in the expense worksheet and the next destination cell.
Set rngDAFRPCA = rngDAFRPCA.Offset(1, 0)
'rngGMPCA = rngGMPCA.Offset(1, 0)
'Sheets("Prepared_Expense").Select
Set rngDestin = rngDestin.Offset(1, 0)

Else
'Moves to next DAFR PCA and test for match.
'rngDAFRPCA = rngDAFRPCA.Offset(1, 0)

'Moves to next GM PCA and test for match.
Set rngGMPCA = rngGMPCA.Offset(1, 0)
Set rngToCopy = rngToCopy.Offset(1, 0)
'Records comment in cell with DAFR PCA to alert user to non-matching problem _
all the DAFRPCAs have been tested.

If intCounter = intStop Then
rngDAFRPCA.Comment = "DAFR PCA has no match in GM worksheet, please research."
rngGMPCA.Comment = "DAFR PCA has no match in GM worksheet, please research."
rngDestin.Comment = "No data retreived."

'Moves to next row in both worksheets and a new destination cell.
Set rngDAFRPCA = rngDAFRPCA.Offset(0, 0)
Set rngGMPCA = rngGMPCA.Offset(1, 0)
Set rngDestin = rngDestin.Offset(1, 0)
intCounter = 0
End If
End If
'Increments counter.
intCounter = intCounter + 1
Loop
End Sub

 

[PHV]

A better way to find the last row:
LastRow = Sheets("First QT").Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
The CurrentRegion don't collapse empty cell.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[RSJohnson]

Thanks I will try your suggestion.