workaround for writing a variant array to worksheet using the Transpose Function

[Xceller]

Hi all - I have a 10,000 X 26 range and want to consolidate it into a single column. There are 260,000 elements in the array. The worksheet function Transpose only handles 65,000 elements. I am looking for a workaround solution. Maybe looping thru the array and write it out in 65,000 increments to a sheet.

Appreciate your help.

my code:

Sub ConsdRange()


Dim Arr As Variant
Dim nuArr() As Variant


Arr = Range("A1:Z100000")


ctr = 1
For c = 1 To 26
    For r = 1 To 10000
        ReDim Preserve nuArr(1 To ctr)
        nuArr(ctr) = Arr(r, c)
        ctr = ctr + 1
    Next
Next


Cells(1, "AB").Resize(UBound(nuArr)) = Application.Transpose(nuArr())


End Sub

 

[shg]

Untested:

Sub ConsdRange()
    Const nRow      As Long = 10000
    Dim iRow        As Long
    Dim iCol        As Long

    iRow = 1
    For iCol = 1 To 26
        Cells(iRow, "AB").Resize(nRow).Value = Columns(iCol).Resize(nRow).Value
        iRow = iRow + nRow
    Next iCol
End Sub

 

[Xceller]

Thanks shg for the code. What I really want to do is to find a workaround for the Transpose function. If I have a collection of elements in a variant array that exceeds what the Transpose function can handle. Is there a way to spit out the whole array to a worksheet?

For example, If I collect all numbers > 500 then write them out to a column.

Sub ConsdRange()


Dim Arr As Variant
Dim nuArr() As Variant


Arr = Range("A1:Z100000")


ctr = 1
For c = 1 To 26
    For r = 1 To 10000
        ReDim Preserve nuArr(1 To ctr)
        If Arr(r, c) > 500 Then
            nuArr(ctr) = Arr(r, c)
            ctr = ctr + 1
        End If
    Next
Next


Cells(1, "AB").Resize(UBound(nuArr)) = Application.Transpose(nuArr())


End Sub

 

[RoryA]

I don't really see how Transpose would help you here anyway, but here's one alternative: Excel Matters » Blog Archive » Transposing an array using an in-memory listbox

The function code inline:

Function TransposeIt(vData)
   Dim lBound2                     As Long
   If IsArray(vData) Then
      ' test for 1D array
     On Error Resume Next
      lBound2 = UBound(vData, 2)
      On Error GoTo 0
      ' create MSForms.ListBox
     With CreateObject("New:{8BD21D20-EC42-11CE-9E0D-00AA006002F3}")
         .Column = vData
         If lBound2 = 0 Then
            ' for 1D, returning the Column will transpose and return 2D array
           TransposeIt = .Column
         Else
            ' for 2D array just return the List
           TransposeIt = .List
         End If
      End With
   End If
End Function

You can also simply create another array with the dimensions reversed, and loop through the first array to populate the second.

 

[Xceller]

@RoryA, Thank you for your comment. I just updated my last post with an example. Let's say I want to test if certain conditions are true in a dataset and collect that data in an array, and if that collection in the array is 260,000 or more elements. How do I write it out to a worksheet? Maybe not using the Transpose function but that's only function I know due to my limited knowledge in vba.

 

[RoryA]

I've given you an alternative to Application.Transpose - just use that instead.