|
Error Code 6
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 |
Error Code 6
By the way, the for statement actual is
For J = 20 To lastrow "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 |
Error Code 6
Dave -
Your loop variable (J) is Integer type, which has a range of -32,768 to 32,767. You are probably getting the overflow error when it tries to go past this range. Try changing the type of J to Long. -- In theory, there is no difference between theory and practice; in practice, there is. "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 |
Error Code 6
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 |
Error Code 6
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 |
Error Code 6
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 |
| All times are GMT +1. The time now is 11:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com