View Full Version : Selecting Rows with More than one Constant
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
06-24-2011, 07:46 AM
How do you get an error message when you have On Error Resume Next?
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
06-24-2011, 07:52 AM
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
06-24-2011, 07:53 AM
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.
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.
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.
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.