+ Reply to Thread
Results 1 to 10 of 10

Run Time Error '6'-OverFlow

  1. #1
    Registered User
    Join Date
    11-10-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    18

    Run Time Error '6'-OverFlow

    Hey guys, I am getting a Run time error '6', overflow. I am running a macro that deletes blank rows and my file contains more than 50K records. Within these record there are alot of blank spaces that I am trying to get rid of and. I've tried to debug it but no luck. The error starts at (For i = 1 To Counter)-(this is where I tried to input how many rows to go through and delete), I have posted the macro below. Thanks in advance for the help.

    Please Login or Register  to view this content.

  2. #2
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Run Time Error '6'-OverFlow

    Hi mjali001
    Try this
    Please Login or Register  to view this content.
    rather than this
    Please Login or Register  to view this content.
    Why??? See this http://msdn.microsoft.com/en-us/libr...ffice.10).aspx
    Last edited by jaslake; 04-02-2012 at 11:12 AM.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Registered User
    Join Date
    11-10-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: Run Time Error '6'-OverFlow

    Hey Jaslake, thanks soo much for your insight. I've used your suggestion, the macro seemed to work at first but it looks like it timed out or something. After the 3000 thousandth row it stopped working. I've used a portion of the macro to see if I can isolate the problem but I've hit a road bump. Below is a portion of the macro with the suggested changes.

    Sub Macro7()
    '
    ' Macro7 Macro
    '
    Range("A10:C10").Select
    Selection.ClearContents

    Sheets("Factory Billing Data").Select
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(0, 2), Array(21, 1), Array(51, 1), Array(60, 1), Array(75, 1), _
    Array(94, 1), Array(116, 1)), TrailingMinusNumbers:=True
    Columns("A:G").Select
    Columns("A:G").EntireColumn.AutoFit

    Dim Counter
    Dim i As Long

    Counter = InputBox("Enter Amount of Rows to Delete")
    ActiveCell.Select
    For i = 1 To Counter
    If ActiveCell = "" Then
    Selection.EntireRow.Delete
    Counter = Counter - 1
    Else
    ActiveCell.Offset(1, 0).Select
    End If

    Next i
    '
    End Sub

  4. #4
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Run Time Error '6'-OverFlow

    Hi mjali001
    Any chance you can post your file? It seems to me you should not hve to interate through the range to find blank cells. Have you tried
    Please Login or Register  to view this content.
    Last edited by jaslake; 04-02-2012 at 03:16 PM.

  5. #5
    Registered User
    Join Date
    11-10-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: Run Time Error '6'-OverFlow

    Jaslake thanks so much for your insight. I wish I can upload the file but it contains our clients account numbers and name information and I can't seem to find a good replica of this file. I've made some adjustments to the macro and it seems what you suggested works, but the problem comes after the deletion of the row. It's when I get to part for the auto filter to delete the remaining rows that contain blanks in column A the macro seems unresponsive. Can you offer any insight, I would greatly appreciate it.


    With ActiveSheet
    .AutoFilterMode = False
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
    .AutoFilter 1, ""
    On Error Resume Next
    .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False

  6. #6
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Run Time Error '6'-OverFlow

    Hi mjali001
    On a COPY of the file, change the account numbers and names. In the account number column, for the first record type in 101; in the second record type 102. Highlight both records then drag down to the last row. In the name column, for the first record type sam1; in the second record type sam2. Highlight both records then drag down to the last row.

  7. #7
    Registered User
    Join Date
    11-10-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: Run Time Error '6'-OverFlow

    Hey Jaslake, the original file is in text format and I'm actually using this macro to re-format it and it's extremely difficult for me to go through and replace the acct# and name. I however got the macro to work pass deleting the rows and now the problems lies when auto filter script arise. I will have two code's listed below, the first on is how far the macro works and the second is where the new problem arises. If this is not very helpful I will try to manually replicate the data in a new workbook. Thanks again for your insight!

    This is how far the macro works till

    ' Range("A10:C10").Select
    Selection.ClearContents

    Sheets("Factory Billing Data").Select
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(0, 2), Array(21, 1), Array(51, 1), Array(60, 1), Array(75, 1), _
    Array(94, 1), Array(116, 1)), TrailingMinusNumbers:=True
    Columns("A:G").Select
    Columns("A:G").EntireColumn.AutoFit

    Dim Counter
    Dim i As Long

    Counter = InputBox("Enter Amount of Rows to Delete")
    ActiveCell.Select
    For i = 1 To Counter
    If ActiveCell = "" Then
    Selection.EntireRow.Delete
    Counter = Counter - 1
    Else
    ActiveCell.Offset(1, 0).Select
    End If

    Next i

    Columns("A:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    ActiveCell.FormulaR1C1 = "=IF(LEFT(RC[2],3)=""000"",R[1]C[2],"""")"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=IF(LEFT(RC[1],3)=""000"",R[1]C[2],"""")"
    Range("A1:B1").Select
    Selection.AutoFill Destination:=Range("A1:B50000")
    Range("A1:B50000").Select
    ' Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False

    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    This is when the macro bugs or doesn't work (I'm using the auto filter to delete the rows with blanks in column A, but it doesn't seem to be working)

    With ActiveSheet
    .AutoFilterMode = False
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
    .AutoFilter 1, ""
    On Error Resume Next
    .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False

    Columns("E:E").Select
    Selection.Delete Shift:=xlToLeft
    Sheets("Factory Billing Macro").Select
    Range("A1:G1").Select
    Selection.Copy
    Sheets("Factory Billing Data").Select
    Range("A1").Select
    ActiveSheet.Paste

    Columns("A:G").Select
    Columns("A:G").EntireColumn.AutoFit
    Columns("A:A").Select
    Application.CutCopyMode = False
    With Selection
    .HorizontalAlignment = xlRight
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Columns("B:B").Select
    With Selection
    .HorizontalAlignment = xlRight
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Columns("E:E").Select
    Selection.Style = "Currency"
    Columns("F:F").Select
    Selection.Style = "Currency"
    Columns("G:G").Select
    Selection.Style = "Currency"

    ActiveWorkbook.RefreshAll

    Sheets("Factory Billing Macro").Select

    Range("A10").Select
    ActiveCell.FormulaR1C1 = "=SUM('Factory Billing Data'!R[-8]C[4]:R[4990]C[4])"
    Range("A10").Select
    Selection.AutoFill Destination:=Range("A10:C10"), Type:=xlFillDefault
    Range("A10:C10").Select


    End With
    End Sub

  8. #8
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Run Time Error '6'-OverFlow

    Hi mjali001
    I can't help much without seeing your file. You may want to take a look at this link
    http://www.rondebruin.nl/specialcells.htm

  9. #9
    Registered User
    Join Date
    11-10-2011
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: Run Time Error '6'-OverFlow

    Jaslake, I just solved the problem with the macro, I added a sort function. Thanks alot for all your help. I've also attach the macro below if you wanted to see what it looks like now.

    Sub Macro15()
    '
    ' Macro15 Macro


    ' Range("A10:C10").Select
    Selection.ClearContents

    Sheets("Factory Billing Data").Select
    Columns("A:A").Select
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
    FieldInfo:=Array(Array(0, 2), Array(21, 1), Array(51, 1), Array(60, 1), Array(75, 1), _
    Array(94, 1), Array(116, 1)), TrailingMinusNumbers:=True
    Columns("A:G").Select
    Columns("A:G").EntireColumn.AutoFit

    Dim Counter
    Dim i As Long

    Counter = InputBox("Enter Amount of Rows to Delete")
    ActiveCell.Select
    For i = 1 To Counter
    If ActiveCell = "" Then
    Selection.EntireRow.Delete
    Counter = Counter - 1
    Else
    ActiveCell.Offset(1, 0).Select
    End If

    Next i

    Columns("A:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    ActiveCell.FormulaR1C1 = "=IF(LEFT(RC[2],3)=""000"",R[1]C[2],"""")"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=IF(LEFT(RC[1],3)=""000"",R[1]C[2],"""")"
    Range("A1:B1").Select
    Selection.AutoFill Destination:=Range("A1:B50000")
    Range("A1:B50000").Select
    ' Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False

    Range("A1:I50001").Select
    Range("A1").Activate
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Factory Billing Data").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Factory Billing Data").Sort.SortFields.Add Key:= _
    Range("A1:A50001"), SortOn:=xlSortOnValues, Order:=xlDescending, _
    DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Factory Billing Data").Sort
    .SetRange Range("A1:I50001")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    ActiveWindow.SmallScroll Down:=-5

    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    With ActiveSheet
    .AutoFilterMode = False
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
    .AutoFilter 1, ""
    On Error Resume Next
    .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False

    Columns("E:E").Select
    Selection.Delete Shift:=xlToLeft
    Sheets("Factory Billing Macro").Select
    Range("A1:G1").Select
    Selection.Copy
    Sheets("Factory Billing Data").Select
    Range("A1").Select
    ActiveSheet.Paste

    Columns("A:G").Select
    Columns("A:G").EntireColumn.AutoFit
    Columns("A:A").Select
    Application.CutCopyMode = False
    With Selection
    .HorizontalAlignment = xlRight
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Columns("B:B").Select
    With Selection
    .HorizontalAlignment = xlRight
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Columns("E:E").Select
    Selection.Style = "Currency"
    Columns("F:F").Select
    Selection.Style = "Currency"
    Columns("G:G").Select
    Selection.Style = "Currency"

    ActiveWorkbook.RefreshAll

    Sheets("Factory Billing Macro").Select

    Range("A10").Select
    ActiveCell.FormulaR1C1 = "=SUM('Factory Billing Data'!R[-8]C[4]:R[49990]C[4])"
    Range("A10").Select
    Selection.AutoFill Destination:=Range("A10:C10"), Type:=xlFillDefault
    Range("A10:C10").Select



    End With
    '

    '
    End Sub

  10. #10
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Run Time Error '6'-OverFlow

    Hi mjali001
    You're welcome...glad I could help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1