Copy Paste Loop Runtime Error 6

Question

I have the following table:

Code     Year
8948KH   2003
         2004
         2005
         2006
923587   2003
         2004
         2005
         2006
938972   2003
         2004
         2005
         2006

Assume "Code" is in cell A1. I want the values of 8948KH, 923587 and 938972 to copy/paste themselves until they run into another code.

To do this I used the following code. which I found on Stackoverflow made by D Mason:

Sub replaceBlanks()

' define variables
Dim column As Integer
Dim row As Integer
Dim lastRow As Integer
Dim previousValue As String
Dim value As String

' stop screen from updating to speed things up
Application.ScreenUpdating = False

' use the active sheet
With ActiveSheet

    ' get the current cell selected and the last row in column selected
    column = ActiveCell.column
    row = ActiveCell.row
    lastRow = .Cells(.Rows.Count, column).End(xlUp).row

    ' set previous value to the first cell
    previousValue = Cells(row, column).value

    ' iterate for every row between selected and last row with data in
    For i = row To lastRow
        ' set value = the content of that cell
        value = Cells(i, column).value
        ' if it contains nothing
        If Len(value) < 1 Then
            ' set the value of cell equal to the previous cell that had something in it
            Cells(i, column).value = previousValue
        ' if it contains something
        Else
            ' update the previous value and move on to next row
            previousValue = value
        End If

    Next i

End With

' update the screen at the end
Application.ScreenUpdating = True

End Sub

This works fine for very small loops. However, I need to loop approximately 6700 pieces of code spread out over 80.000 rows. Between each code and the next there are exactly 11 blank rows and I need them to stay there to copy the codes into.

If I try to do this, Excel yields a 'Runtime Error 6 Overflow' and refers to lastRow = .Cells(.Rows.Count, column).End(xlUp).row in the debugger.

Is there any way to adjust the macro in order to keep Excel from generating that error?

This is kind of a duplicate question. Please see the answer here: http://stackoverflow.com/a/10558650/42346 — mechanical_meat, Nov 14 '14 at 23:29

score 0 · Accepted Answer · answered Nov 14 '14 at 23:44

Try this tidier alternative.

Sub Fill_in_the_Blanks()
    With Cells(1, 1).CurrentRegion.Columns(1)
        .SpecialCells(xlCellTypeBlanks).Formula = "=A2"
        .Cells.Value = .Cells.Value
    End With
End Sub

This assumes (from your description and sample data) that A2 contains 8948KH and that the cells are actually blank and not zero length strings returned by formulas.

score 0 · Answer 2 · answered Nov 15 '14 at 01:09

0

Select ColumnA, HOME > Editing, Find & Select, Go To Special…, check Blanks, OK, =, Up, Ctrl+Enter

answered Nov 15 '14 at 01:09

pnuts

58,317
11
87
139

Copy Paste Loop Runtime Error 6

2 Answers2