making a global variable array of array in vba and transpose to a sheet

was looking for a solution to this but couldnt find a good explanation.

I currently have code that returns an array for each iteration into a row of a spreadsheet. On the last iteration it take all the row data and copies and pastes it to another sheet. I know it would be a million times faster if I could just make one huge array with each item being a array of data and then transpose it without ever copying and pasting / manipulating cells at all.

How can I do this? The bigger issue for me is also that each major iteration is based off a global variable and is not a loop within the sub itself. This is because the sheet data that is used for making the array for each iteration requires time to load into the sheet.

Here is the code for my sub. As of now you can see that at the each it write the data the Range("A" & pos), which then increments a counter on the sheet itself. Once the sub finishes, the global variable iterates and it assumes again... So in short the array or arrays would need to be a global variable

*Before anyone looks and says F I'm not looking at all that code, the only important part really is like the last 10 lines... everything else is just for the sake of clarity.

Sub find_patternRevised()

Application.ScreenUpdating = False


Sheets("SingleEquityHistoryHedge").Activate

Range("A47:M47").Clear

Dim strt_pt() As Long
Dim end_pt() As Long

Dim i As Long
Dim j As Long
Dim k As Long
Dim y As Long
Dim pos As Long

pos = Range("F1").value


k = 0

For j = 8 To 12

    i = 13

    ' find start points for each column

    Do While Not IsNumeric(Cells(i, j).value)
        i = i + 1
    Loop

        ReDim Preserve strt_pt(4)
        strt_pt(k) = i

        'Debug.Print strt_pt(k)

        k = k + 1
Next j

k = 0


i = 13
j = 8



' finds patterns for each column

Do While j <= 12

    ' find start points for each column
    If Cells(strt_pt(k), j).value > 0 Then
        If Not IsNumeric(Cells(i, j)) Then
            i = i + 1
        Else

            On Error Resume Next ' bypass error thrown by #VALUE
            'loop until return sign changes or cell is blank
            Do Until Cells(i, j).value < 0 Or Cells(i, j).value = vbNullString
                i = i + 1
            Loop

            ReDim Preserve end_pt(5)
            end_pt(y) = i

            'Debug.Print end_pt(y)
            y = y + 1
            j = j + 1
            i = 13 'reset start after entering value

        End If
    ElseIf Cells(strt_pt(k), j).value < 0 Then

        If Not IsNumeric(Cells(i, j)) Then
            i = i + 1
        Else

            On Error Resume Next
            Do Until Cells(i, j).value > 0 Or Cells(i, j).value = vbNullString
                i = i + 1
            Loop

            ReDim Preserve end_pt(5)
            end_pt(y) = i

            'Debug.Print end_pt(y)
            y = y + 1
            j = j + 1
            i = 13 ' reset start after entering value
        End If

    End If




 Loop



Dim lent As Long
Dim end_ct As Long


end_ct = 0

Dim final_array() As Variant
ReDim Preserve final_array(11)


final_array(0) = Range("B2").value
final_array(1) = Range("B3").value

j = 8


For lent = 2 To 11 Step 1


    If lent Mod 2 = 0 Then

        ReDim Preserve final_array(11)
        final_array(lent) = end_pt(end_ct) - strt_pt(end_ct)

    Else

        'gets average over pattern period
        Dim avg_rng As Range
        Set avg_rng = Sheets("SingleEquityHistoryHedge").Range(Cells(strt_pt(end_ct), j), Cells(end_pt(end_ct) - 1, j))

        Dim avg_value As Double
        avg_value = avgVal(avg_rng)

        ReDim Preserve final_array(11)
        final_array(lent) = avg_value

        end_ct = end_ct + 1
        j = j + 1
    End If



Next lent

Range("A" & pos).Resize(1, UBound(final_array) + 1) = final_array

Sheets("SingleEquityHistoryHedge").Range("f1").value = pos + 1



End Sub