Strange excel error for simple macro

[resume]

I'm running a very simple macro that inserts a row if cell = 010 - 070.

this macro works when its tested out on a small worksheet, but when i use it on my spreadsheet with 35k rows, it bugs out. The macro did work yesterday. I also had another macro written differently to do the same thing and the same error occured.

Here is the code

<TABLE style="WIDTH: 894pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1192 border=0><COLGROUP><COL style="WIDTH: 894pt; mso-width-source: userset; mso-width-alt: 43593" width=1192><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 894pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=1192 height=20>Sub addrows()</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>'highlight selection</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>Dim counting As Double</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>Dim rcount As Double</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>ScreenUpdating = False</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>rcount = Selection.Rows.Count</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>ScreenUpdating = False</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>For counting = 0 To rcount</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>Start:</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20> If ActiveCell.Value = "010" Then GoTo extra</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20> If ActiveCell.Value = "020" Then GoTo extra</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20> If ActiveCell.Value = "030" Then GoTo extra</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20> If ActiveCell.Value = "040" Then GoTo extra</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl70 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20> If ActiveCell.Value = "050" Then GoTo extra</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20> If ActiveCell.Value = "060" Then GoTo extra</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20> If ActiveCell.Value = "070" Then GoTo extra</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>ActiveCell.Offset(1, 0).Select</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl74 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15.75pt; BACKGROUND-COLOR: white" height=21> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>Next counting</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20> Exit Sub</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>extra:</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20> ActiveCell.Offset(1, 0).EntireRow.Insert</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20> ActiveCell.Offset(2, 0).Select</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20> GoTo Start</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl73 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: white" height=20>End Sub

</TD></TR></TBODY></TABLE>
When i run the macro, when it gets to the point where a row is inserted, the Macro code inserts itself all the way across the row everything sorta crashes. Also if you know of a more efficient code that would be great. Here is a screenshot:

 

[Smitty]

Welcome to the Board!

I've sure never seen that before, so it makes me think that you overwhelmed your processor. Looping though 35k rows and selecting like that is horribly inefficient.

I'd try adding an AutoFilter, filter on 010, 020, 030, etc., then Goto-->Special-->Visible Cells Only-->Insert Row.

HTH,

 

[resume]

Thank you very much Smitty

 

[jbeaucaire]

Whenever I insert rows:

1) Never work with "activecell", selecting cells like humans do horribly slows down your macros. Instead reference cell values and issue commands directly to the sheet without "selecting" or "activating".

2) Insert rows from the bottom up so it's easier to not run into yourself.

Dim LR as Long, Rw as Long

Application.ScreenUpdating = False
LR = Range("J" & Rows.Count).end(xlUp).Row

For Rw = LR to 2 Step -1
   Select Case Range("J" & Rw).Value
      Case "010", "020", "030", "040", "050", "060", "070"
          Rows(Rw+1).Insert xlShiftDown
   End Select
Next Rw

Application.ScreenUpdating = True