LinkBack URL
About LinkBacks
Hi. I'm wondering if there's a way to paste a Jagged array to cells in a single pass? Here's the loop I'm thinking of?
this relates in some way to the following
Please Login or Register to view this content.HTML Code:
Hi. I'm wondering if there's a way to paste a Jagged array to cells in a single pass? Here's the loop I'm thinking of?
this relates in some way to the followingPlease Login or Register to view this content.HTML Code:
Last edited by cmore; 03-24-2017 at 07:57 PM.
Please edit your post to add CODE tags.
Thanks.
Entia non sunt multiplicanda sine necessitate
Sorry about that, updated.
I don't know of a way to paste a jagged array to a sheet without first converting it into a 1D or 2D array.
If speed is the issue, this would be a fast method. It has four sheet reads (one for each range area) and one sheet write. The sheet read\writes are the expensive part. Transfering values between arrays is inexpensive.
Please Login or Register to view this content.
Surround your VBA code with CODE tags e.g.;
[CODE]your VBA code here[/CODE]
The # button in the forum editor will apply CODE tags around your selected text.
Yep speed is the problem. I'm working at implementing this. The problem is that the final solution is currently 262 rows instead of 4 and will expand another 100 or so at least. so I'm looking at cycling through the range on another sheet 300 or so times and then some of the strings will be duplicates (not an issue here really) the big issue is that sub loop, because each item is 100 or so rows and I'm looking at a loop of well over 30,000 rows. I've found ways to manipulate matricies with the MMULT function but extracting text has been the primary nightmare.
You're welcome. I didn't really follow much after; "The problem is..."Originally Posted by cmore
Def. Understand. Short story long. In theory I wish I could convert this to a single array but looping through each element of the sub-rows won't be a final solution because there's too much data.
I thought you wanted a single array?
I have no idea what a sub-row is either.
Sorry, yeah I do want a single array so. In my example I have an array of 4 elements and then each of the elements has 50 elements in them, so overall I have 200 elements. Your solution gets me to a single array with 200 elements. This is what I really want, but the real project is 262+ elements with about 100 elements each, so that sub-loop the For-Next loop, will result in my doing 262 loops with the for each x 100 with the For-Next.
The sub-array to consolidated array transfer is very quick. That's not the problem. And the size of the arrays is not the issue.
In this example, you'll read 262 ranges of 100 cell each and then write to one range of 26,200 cells. It doesn't matter the size of each range. The slow part is each time you access the sheet. So if you use the method I suggested, you'll have 262 sheet reads and 1 sheet write. That is not excessive. It should take less that a second for the whole process.
According to the OP, is it just coincidence that the arrays you mention are sequential? If they will actually be sequential then I dont see any reason to have separate array elements for each. You could add the full range all at once. Maybe a sample of the data layout and goal would help.
How you move data between sheets can depend on a couple things. I have found via testing that the fastest way is to assign the source range to an array and then set the value of the destination range to the array. Essentially you declare a variant variable as your array like:
Then you assign the range to it, like (presuming rngData is the source range):Please Login or Register to view this content.
This creates a 2D array implicitly. The 1st dimension is the rows and second dimension is the columns. Even if you do a single column or a single row you will still have a 2D array.Please Login or Register to view this content.
Here's the great part. To feed this to destination range you can either chose the cell that will be the top left most corner of the destination range and resize it according to the Ubounds of the 1st and second dimensions of the array or use a range object for the destination that is already the same dimensions as the source and then just assign that destination ranges value from the array direct. Something like:
(presuming using top left cell of destination and resizing, rngDest could have been set prior to lets say B2)
Then to dump the data back you could do:Please Login or Register to view this content.
I have attached a sample of this methodology. In my testing for speed I used 2 files (not attached). 1 with the macro code similar to my sample and another with 10 sheets, 65k rows x 8 or 10 columns each. I used this method to copy all 650k rows from those 10 sheets to the macro file on 1 sheet. The entire macro took, on my machine at work(i7 3770), ~17 seconds including the time it took to open the data file. I also timed it excluding the time to open the data file. I first tried that with 250k rows of data and it took on avg 4.1 seconds to copy 250k rows. With 650k rows it went up to...avg of 4.8 seconds. This method appears to scale really well. Doing 650k rows with any other method either froze, crashed, or took forever in my testing.Please Login or Register to view this content.
My attached file has a lengthy explanation and details. It shows dumping range - range and table to table.
If you are looking for speed this is the way to go. I think in my above testing I had a routine that appended arrays. Im not at work so I cant look at that routine but Im pretty confident I based my code for appending the arrays off code on Chip Pearsons site in a post/page titled VBA Arrays and the function titled "CombineTwoDArrays". I would link to it but I am relatively new and am not sure what the rules are to linking to other sites. Google should pull it up easily.
Hope this helps
PS - the appending arrays is likely the key to this working as you might need. This eliminates the array in an array or jagged array. Instead you could pass each sub range (assuming the same # of columns) to the function to append to the array and when the array is complete just dump it to the destination.
Last edited by Zer0Cool; 03-24-2017 at 11:45 PM.
I presumed the ranges are not contiguous in his actual data set. I could be wrong of course.
Yea I was betting on that not being the case but figured id mention it.
I forgot to mention, the sample I posted will also debug.print some info to the immediate window in the VBE. If you dont have the immediate window open you can enable it from View | Immediate window. Then when you run my macros it will display some information that may help detail what has happened.
AlphaFrog, have done some digging, and I think you might be right, I think the issue is writes. So I'm going to leverage what you give me and get back in a bit. I have this situation for about 10 or so columns so I'll read them into the jagged then switch each of the columns to a single array then paste. Toes crossed
Zero0Cool, this is cool, I oversimplified the issue in my og post. the ranges will be non-contiguous so cannot do a direct read.
I think you now see the light.
Another tip: If you first read all the data into a jagged array, you could then calculate the size needed for the consolidated array and not use the ReDim Prserve I used in my suggested code. That would help speed things up a bit as well.
I figured, which is why I mentioned appending arrays. Presuming your destination is a continuous range then this is the fastest method of moving data (otherwise it would be the range to array to range method in a loop). What you are trying to do is an array of arrays (jagged) if I understand right, which is more complex then what you really need. With an array of arrays you need to not only keep track of where in the parent array you are, but which element of the sub array. By appending the 2D array you do not need to keep track of anything other then the final dimensions (Ubounds) which is super simple.
If you look for the site I mention he literally gives you the function to do this and many other helpful array functions. If you combine my method of going from range to array to range with the appending arrays you will have what I have found in many hours of testing at work to be the fastest method for moving large data sets. As I mentioned in my post before, my large scale testing was from data across 10 sheets, so not continuous ranges either. Your not going to get much faster then moving 650k rows x 10 columns in under 5 seconds.
If I remember maybe I can dig up the code for my large scale sample when Im at work and post it.
whooo. So I tried, your way AlphaFrog. It's solid AlphaFrog, and I also tweaked the data to get rid of some calcs that were somehow running when I pasted the data even if I had calcs off. and the pasting 100 elements at a time in a loop of 262 elements is similar in speed. That said, I kind of settled on using the MMULT function (for now), as it takes the info and with a little math and a prayer I consolidate info. into a single array. Takes a few steps to get there, so I'm not sure it'll make any sense to post, but here's a bit of it:
**It won't let me post the code, so I'll just post the final conversion line, and hopefully this makes sense. I basically calculate starting and ending from 1 sheet and starting from 2nd sheet and then create an array in terms of the 3rd sheet counter using the formula below.
Please Login or Register to view this content.
Zer0Cool, again I like your idea, but I think maybe there's something missing. so the thing is I have 1 sheet let's say rows 1:300 of like unique IDs that correspond to info in sheet 2 and let's say these rows are 1:8000 and then I want to populate sheet 3 that takes sheet 1 info that is random with respect to sheet 2 such that when I populate sheet 3 the information will be non-contiguous.
So I checked a number of things, for me at least seems like doing what you need to do to get the arrays into parameters that can work into a MMULT is the fastest way. It's all still not instantaneous, so depends on how much data your parsing. Thanks for all your time, very much appreciated!
Hi !
To try on a new workbook :
PHP Code:Sub Demo1()
Dim V(1 To 262), L&(1 To 100), N&
For N = 1 To UBound(V): V(N) = L: Next
[A1].Resize(UBound(V), UBound(L)).Value = Application.Index(V, 0)
End Sub
Last edited by Marc L; 04-16-2017 at 08:02 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks