Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 536
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 147
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 153
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Claus code works on my data not on OP's data Howard Excel Programming 2 August 14th 13 08:10 PM
Copy all data into one sheet and insert origin data... code amend... [email protected][_2_] Excel Programming 3 September 15th 08 02:54 PM
VBA code for moving data from even rows to columns after data in oddrows Steve G[_4_] Excel Programming 4 July 5th 08 05:26 PM
Excel code from 7 day data to 1 day data-pivot tables LOrama Excel Programming 0 September 20th 07 04:52 PM
Filtering data via VBA code [email protected][_2_] Excel Programming 2 April 10th 07 02:43 PM


All times are GMT +1. The time now is 03:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"