code break

[Dave B]

I have some code which tests for a condition and deletes unwanted rows:

For i = 2 To intLastRow

strCellText = ActiveCell.Text
intLocInStr = InStr(1, strCellText, "sample text", vbTextCompare)

If intLocInStr > 0 Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.EntireRow.Delete
intLocInStr = 0
End If

strCellText = ""

Next i

Sometimes, after the ActiveCell.EntireRow.Delete line, the code breaks as if
I had pressed ctrl-Break. I get the message "Code execution has been
interrupted" with options to End, Debug, etc.

I don't have breakpoints or anything. Has anyone ever had this problem or
know why my code keeps breaking? (It's pretty annoying to have to sit there
and keep pressing C ... for Continue.)

 

[Dave B]

I tried from the bottom up and I will use that from now on, but it is still
breaking. The sheet is not protected and there are no merged cells. It
doesn't break every loop. It breaks after about 5-10 loops through the
EntireRow.Delete line. When it is not deleting it's fine.

When I start my computer, I can run the code a few times clean through. But
then after a few clean runs it starts breaking. I have Windows ME and
Office 2000 on a Dell Dimension 8200 with an Intel Pentium 4. The heat sink
isn't getting too hot. Any idea what else it could be?

I'm thinking it might be my version of VBA. The Undo and Redo commands
don't work correctly either. Thanks for any advice you guys can come up
with.

 

[Tom Ogilvy]

Undo history is cleared by many macro actions - so that may not be an
indicator.

If you use this workbook a lot, it is possible that you could benefit from
cleaning you project with Rob Bovey's code cleaner.
http://www.appspro.com go to free downloads.

This basically exports all your code components, deletes them, saves the
file, then imports the components back in.

Many times this can clean up weird behavior.

You haven't said what error message you get when it breaks.

Regards,
Tom Ogilvy

 

[Dave B]

Hey Dave. Thanks for all the help so far. The code is in a regular module,
the active cell is definitely the correct one, and there are no worksheet
events. The only add-ins I have hooked in are Solver, Access Links,
Analysis Toolpack (which I think is from John Walkenbach), and in VBA the
only thing in the project window other than the current workbook is
Funcres.xla.

Basically, I have all this data in text files that I am formatting into a
useful format. So I open the text file with Excel and save it as .xls, then
I import the module and run it. So the workbooks are new workbooks and it's
still blowing up.

The weird thing is that when I first turn my computer on, I can import the
code and run it on four or five workbooks without any breaks at all before
it starts breaking. That's why I mentioned that the heat sink wasn't
getting too hot, because I figure it must be something that changes from
when the computer is first started. I don't know much about memory and
processor usage though, so I'm out of ideas.

 

[Dave B]

Hey Tom,

Thanks for the info on Undo and Redo. The message that I get is the exact
same window that pops up when you press break:

"Code execution has been interrupted

Continue End Debug Help"

I posted a longer reply to Dave Petersen's message if you have any more
ideas for me. Thanks for all your help so far.

 

[Dave Peterson]

If you do the same stuff on a different pc, does it break, too?

(I don't have any guesses left. Sorry.)