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

Xceller

Active Member
Joined
Aug 24, 2009
Messages
265
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:
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,714
Office Version
2010
Platform
Windows
Untested:

Code:
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

Active Member
Joined
Aug 24, 2009
Messages
265
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.

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)
        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
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,327
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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:
Code:
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

Active Member
Joined
Aug 24, 2009
Messages
265
@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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,327
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
I've given you an alternative to Application.Transpose - just use that instead.
 

Forum statistics

Threads
1,081,841
Messages
5,361,630
Members
400,642
Latest member
tekster23

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top