Selecting Rows with More than one Constant

[Opv]

I have stared at this code until it's all a blur and I can't seem to figure out why I'm receiving an error. The line highlighted when the error message is received is the line in which SELECT is used. I'm hoping a fresh pair of eyes can point me to the problem.


Dim lRow As Integer 'Last Row
Dim lCol As Integer 'Last Column
Dim i As Long

lRow = Range("A1").End(xlDown).Row
lCol = Range("A1").End(xlToRight).Column

On Error Resume Next

Do Until WorksheetFunction.CountA(Rows(lRow).SpecialCells(xlCellTypeConstants)) > 1

Cells(lRow).Offset(-i, 0).Resize(i + 1).EntireRow.Select

Loop

[Bob Phillips]

How do you get an error message when you have On Error Resume Next?

[Opv]

When I run the code a message pops up that says "Code execution has been interrupted." When I click "Debug" the designated line is highlighted, so I presumed there was something wrong in that particular line of code.

[Bob Phillips]

Funny, we have been talking about this over at Smurf today, http://smurfonspreadsheets.wordpress.com/2011/06/23/code-execution-has-been-interrupted/#comment-17022

I think you just hit continue.

[mikerickson]

I see two issues. One is that you are using only one argument for Cells. This might not be a problem, or it might hide a problem.

Of more import is that the loop is not changing lRow, so each loop is doing the same thing and the testing condition is not changing.

[Opv]

I see two issues. One is that you are using only one argument for Cells. This might not be a problem, or it might hide a problem.

Of more import is that the loop is not changing lRow, so each loop is doing the same thing and the testing condition is not changing.

Ah.... I added the line "lRow = lRow-1" to the loop and the message stopped popping up.

[Opv]

I just noticed another problem. I had changed from a For Next loop to a Do While loop and left a couple of "i" references in the code. I just need to totally restructure my loop.

[Opv]

For what it's worth, I came across the following related link:

http://www.mymindleaks.com/blog/article/fix-for-code-execution-has-been-interrupted-in-excel-vba-macros.html