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.

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

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

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

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