Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too much data for this code?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too much data for this code?
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. you only have 1.048.576 rows. With 150 values per column you only can move 6990 columns to one column Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too much data for this code?
On Friday, October 4, 2013 8:15:26 AM UTC-7, Claus Busch wrote:
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. you only have 1.048.576 rows. With 150 values per column you only can move 6990 columns to one column Regards Claus B. Well, that for sure makes sense. One of the most obvious to boot. Thanks Clause. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too much data for this code?
Howard wrote:
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 too much data. I do not rememebr exactly how much you can "paste" as .value = array but it is not large number. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Too much data for this code?
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Claus code works on my data not on OP's data | Excel Programming | |||
Copy all data into one sheet and insert origin data... code amend... | Excel Programming | |||
VBA code for moving data from even rows to columns after data in oddrows | Excel Programming | |||
Excel code from 7 day data to 1 day data-pivot tables | Excel Programming | |||
Filtering data via VBA code | Excel Programming |