First off, the main body of this code is way above my pay grade of vba.

I googled it for a poster who has 8400+- columns by 150 rows data set. The goal is to take each column and move them to a single column, each below the previous, in column A which would essentially then be A1:A1260000.

The instructions with the code is paste into a standard module, select the data on the worksheet and run the code.

I did name the data range "MyBigRng" and added the first line of application goto to select that named range.

Works fine with much smaller data sets and it works if I select around 4200 columns. Not sure what the upper limit is but the code does nothing with 8400 columns.

I have tried to think what Excel limit that might be exceeded here but have no ideas.

Thanks,
Howard


Option Explicit

Sub MakeOneColumn()

Application.Goto Reference:="MyBigRng"

Dim vaCells As Variant
Dim vOutput() As Variant
Dim i As Long, j As Long
Dim lRow As Long

If TypeName(Selection) = "Range" Then
If Selection.Count 1 Then
If Selection.Count <= Selection.Parent.Rows.Count Then
vaCells = Selection.Value
ReDim vOutput(1 To UBound(vaCells, 1) * UBound(vaCells, 2), 1 To 1)
For j = LBound(vaCells, 2) To UBound(vaCells, 2)
For i = LBound(vaCells, 1) To UBound(vaCells, 1)
If Len(vaCells(i, j)) 0 Then
lRow = lRow + 1
vOutput(lRow, 1) = vaCells(i, j)
End If
Next i
Next j
Selection.ClearContents
Selection.Cells(1).Resize(lRow).Value = vOutput
End If
End If
End If
End Sub