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.)

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" wrote in message
...
While I agree with Dave that working from the bottom up is a better way to
go, your code works fine for me.

I assume your sheet isn't protected and you don't have any merged cells on
the sheet.

Regards,
Tom Ogilvy




Dave B wrote in message
...
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.)

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 Peterson" wrote in message
...
Sorry for not checking your code, but when I ran this slightly changed
version,
it worked ok:

Option Explicit
Sub testme01()

Dim i As Long
Dim intLastRow As Long
Dim strCellText As String
Dim intLocInStr As Long

Cells(2, ActiveCell.Column).Select

intLastRow = 440
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

End Sub

When it blows up, what's in the activecell/row? Are you sure that the
activecell is in the right column for your data?

Where is your code? Is it in a general module? Do you have any worksheet
events under that worksheet?

Does this code work for you if you copy it to a new workbook and try it
there?

If yes, you may want to try exporting the module to a .bas file, delete
the
module and reimport the .bas file. (Rob Bovey has an addin that
mechanizes
this. It's his code cleaner addin and can be found at:
http://www.appspro.com)



Dave B wrote:

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" wrote in message
...
While I agree with Dave that working from the bottom up is a better
way to
go, your code works fine for me.

I assume your sheet isn't protected and you don't have any merged
cells on
the sheet.

Regards,
Tom Ogilvy




Dave B wrote in message
...
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 Peterson

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

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

Dave B wrote:

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 Peterson" wrote in message
...
Sorry for not checking your code, but when I ran this slightly changed
version,
it worked ok:

Option Explicit
Sub testme01()

Dim i As Long
Dim intLastRow As Long
Dim strCellText As String
Dim intLocInStr As Long

Cells(2, ActiveCell.Column).Select

intLastRow = 440
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

End Sub

When it blows up, what's in the activecell/row? Are you sure that the
activecell is in the right column for your data?

Where is your code? Is it in a general module? Do you have any worksheet
events under that worksheet?

Does this code work for you if you copy it to a new workbook and try it
there?

If yes, you may want to try exporting the module to a .bas file, delete
the
module and reimport the .bas file. (Rob Bovey has an addin that
mechanizes
this. It's his code cleaner addin and can be found at:
http://www.appspro.com)



Dave B wrote:

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" wrote in message
...
While I agree with Dave that working from the bottom up is a better
way to
go, your code works fine for me.

I assume your sheet isn't protected and you don't have any merged
cells on
the sheet.

Regards,
Tom Ogilvy




Dave B wrote in message
...
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 Peterson


--

Dave Peterson