Help fixing run-time error 7

[aquapowers]

I use the macro below to transpose many rows of data into columns. I keep getting the run-time error '7' unless I break the data into ~10,000 rows increments. The count changes, but I'm currently attempting to run the macro with 58,000 rows. Is there anything that can be changed in the following macro to help fix this and get rid of the run-time error?

I don't know VBA and someone was kind enough to create this macro many years ago here on MrExcel.

Sub Step1()
Dim a, b(), i As Long, n As Long, maxCol As Integer, w()
a = Range("a1", Range("a" & Rows.Count).End(xlUp)).Resize(, 3).Value
ReDim b(1 To UBound(a, 1), 1 To Columns.Count)
With CreateObject("Scripting.Dictionary")
    For i = 2 To UBound(a, 1)
        If Not IsEmpty(a(i, 1)) Then
            If Not .exists(a(i, 1)) Then
                n = n + 1: b(n, 1) = a(i, 1): b(n, 2) = a(i, 2): b(n, 3) = a(i, 3)
                .Add a(i, 1), Array(n, 3)
            Else
                w = .Item(a(i, 1)): w(1) = w(1) + 2
                b(w(0), w(1) - 1) = a(i, 2): b(w(0), w(1)) = a(i, 3)
                .Item(a(i, 1)) = w
                maxCol = WorksheetFunction.Max(maxCol, w(1))
            End If
        End If
    Next
End With
With Range("e2")
    .Resize(, maxCol).EntireColumn.ClearContents
    .Resize(, 3).Value = [{"Item","Qty1","Price1"}]
    With .Offset(, 1).Resize(, 2)
        .AutoFill .Resize(, maxCol - 1)
    End With
    .Resize(n, maxCol).Value = b
End With
End Sub

 

[aquapowers]

Hi Fluff, I do have one additional question regarding this macro. The original intent was to have a header row. From what I can tell, the header row is populated starting in row E2, but it is quickly overridden by the data. Is there as simple adjustment to make in the macro to correct this? I'm guessing it would be in the following section...

With Range("e2")
    .Resize(, maxCol).EntireColumn.ClearContents
    .Resize(, 3).Value = [{"Item","Qty1","Price1"}]
    With .Offset(, 1).Resize(, 2)
        .AutoFill .Resize(, maxCol - 1)
    End With
    .Resize(n, maxCol).Value = b
End With

 

[Fluff]

Try

With Range("e2")
    .Resize(, maxCol).EntireColumn.ClearContents
    .Resize(, 3).Value = [{"Item","Qty1","Price1"}]
    With .Offset(, 1).Resize(, 2)
        .AutoFill .Resize(, maxCol - 1)
    End With
    .Offset(1).Resize(n, maxCol).Value = b
End With

 

[aquapowers]

Awesome Fluff... worked perfectly. Thank you again!!

 

[Fluff]

Glad to help.