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

 

[My Aswer Is This]

Since the script your showing does not do what you want. And you have not explained what your trying to do. Would you please explain in detail what your wanting to accomplish
Transpose many rows to columns is not specific for me.

 

[aquapowers]

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
[QUOTE="My Aswer Is This, post: 5769193, member: 292216"]
Since the script your showin

g does not do what you want. And you have not explained what your trying to do. Would you please explain in detail what your wanting to accomplish
Transpose many rows to columns is not specific for me.
[/QUOTE]
The marco takes three columns of data and transposes it into one row with up to nine quantity and rate columns per unique material.

Raw data example:

Material	Scale Quantity	Rate
21 135355	10​	47.29​
21 135355	50​	37.83​
21 135355	250​	22.93​
21 135355	500​	20.33​
21 135355	1000​	19.48​
21 135355	3000​	17.98​
23 135355	500​	21.35​
23 135355	1000​	20.45​
23 135355	3000​	18.88​

Output of macro:
(I add the row headers)

Material

Scale1

Rate1

Scale2

Rate2

Scale3

Rate3

Scale4

Rate4

Scale5

Rate5

Scale6

Rate6

Scale7

Rate7

Scale8

Rate8

Scale9

Rate9

21 135355

10​

47.29​

50​

37.83​

250​

22.93​

500​

20.33​

1000​

19.48​

3000​

17.98​

23 135355

500​

21.35​

1000​

20.45​

3000​

18.88​

 

[Fluff]

How many output columns do you normally get?

 

[aquapowers]

There are a total of 19 output columns. One for the Material and 9 each of Scale Quantity and Rate.

 

[Fluff]

In that case try this change

ReDim b(1 To UBound(a, 1), 1 To 30)

 

[aquapowers]

Yes, this worked with 58,000+ rows without getting the run-time error! Thank you!!
For learning purposes, what does changing the line from "1 To Columns.Count" to "1 to 30" do?

 

[Fluff]

It simply defines the size of the array, with your code you were making it 16,384 columns wide, but with the change I suggested it's only 30 columns wide. Therefore it doesn't need as much memory.

 

[aquapowers]

Got it. Thank you again.

 

[Fluff]

You're welcome & thanks for the feedback.