Too much data for this code?

  • Thread starter Thread starter Howard
  • Start date Start date
H

Howard

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
 
Hi Howard,

Am Fri, 4 Oct 2013 06:45:06 -0700 (PDT) schrieb Howard:
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.
Click to expand...

you only have 1.048.576 rows. With 150 values per column you only can
move 6990 columns to one column


Regards
Claus B.
 
Hi Howard,



Am Fri, 4 Oct 2013 06:45:06 -0700 (PDT) schrieb Howard:






you only have 1.048.576 rows. With 150 values per column you only can

move 6990 columns to one column





Regards

Claus B.
Click to expand...

Well, that for sure makes sense. One of the most obvious to boot.

Thanks Clause.
 
Howard said:
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
Click to expand...

too much data.
I do not rememebr exactly how much you can "paste" as .value = array but
it is not large number.
 
in column A which would essentially then be A1:A1260000.
You're exceeding the rows count ("A1260000"). Max rows =1048576, max columns = 16384.

Regards

Dave
 
Back
Top