Death to Integer! I never Dim Integer (even For i = 1 to 10) unless I call a
routine that requires it!

"Dave" wrote:

Thanks to both of you...that makes sense!!! It worked

"David Hepner" wrote:

Try:

Dim J as Long instead of integer


"Dave" wrote:

I am using lastrow and for some reason I get an overflow error 6 at the "For"
statement. THe spreadsheet has 39,000 lines of data (only 10 columns wide).
THis macro works on all other of my sheets that go up to 26,000 but for some
reason blows up at 39,000.

Anybody have any ideas or advice?

Thanks,
Dave

Public Sub Rpt_Clnr()
Dim J As Integer
Dim Mystr

lastrow = ActiveSheet.UsedRange.Rows.Count

For J = 20000 To lastrow
Mystr = Left((ActiveSheet.Range("A:A").Rows(J).Text), 4)
If Mystr = "Date" Then
ActiveSheet.Rows(J).Delete
J = J - 1
ElseIf Mystr = "CFM " Then
ActiveSheet.Rows(J).Delete
J = J - 1
ElseIf Mystr = " " Then
ActiveSheet.Rows(J).Delete
J = J - 1
ElseIf Mystr = "----" Then
ActiveSheet.Rows(J).Delete
J = J - 1
ElseIf Mystr = "Item" Then
ActiveSheet.Rows(J).Delete
J = J - 1
End If
Next

ActiveSheet.Range("a1").Activate
lastrow = ActiveSheet.UsedRange.Rows.Count
ActiveSheet.Range("A1") = lastrow - 19

End Sub