| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| error with code | Excel Discussion (Misc queries) | |||
| VBA error code | Excel Programming | |||
| Getting error in code | Excel Programming | |||
| How can I still go to the error-code after a On Error Goto? | Excel Programming | |||
| Code Error - Run Time Error 5 (Disable Cut, Copy & Paste) | Excel Programming | |||
Hybrid Mode
