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 |
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 |
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. |
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. |
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 |
All times are GMT +1. The time now is 11:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com