overflow error

[tonyvee1973]

Sub Macro()
Dim nr As Integer, r As Long, i As Integer
nr = Cells(Rows.Count, 5).End(xlUp).Row
For r = nr To 5 Step -1
Select Case Cells(r, 5).Text
Case "36043": i = 24
Case "39013": i = 10
End Select

Rows(r + 1 & ":" & r + i).Insert shift:=xlDown
Next
End Sub



Hiya
Just applied the code above to add blank rows into my evergrowing sheet - when i try to run it i get "Run-time error '6': overflow.Any ideas on why this is and how i can fix?
Thanks

 

[combo]

You can try to assign to 'nr' a number above integers limit.

combo

 

[tonyvee1973]

Hi and thanks.
I know this sounds dumb but any examples of how i'd do that? The sheet is 50,000 rows and growing

 

[Gammachaser]

Dim nr as Long

Integer has an upper limit of 32,767. Long allows up to 2,147,483,647. If that is not enough, get a bigger hammer.

 

[SkipVought]

IMHO, Insert has some pitfalls. Why not append rows at the bottom of your table and sort into position as required. MUCH simpler and FEWER pitfalls.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}