I am trying to use an array to populate a column of around 300,000 rows. The routine gives N/A after 30,000 (10% of what I want). What's the maximum number of elements for an array (to simplify it let's say one dimension - redim arr(1 to bigNum) in excel 2016? How can I get it to work? I can't use loops to populate as it's taking way long. Any help appreciated!
300K rows isn't really a problem. How do you fill your array ?
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
It's the basic manipulation such as taking one column with replacing some values. Did you have no issues with the size of an array at all? Maybe it's just my computer.
I did find a solution but I may not remove this thread since some others may have the same issue. This is one way to do it if the size is too big: redim preserve (1 to bigNum, 1 to 1). It works like a magic. Thanks for the reply!
Last edited by aprildu; 04-15-2019 at 05:55 PM.
Just for the heck of it, I filled 350,000 cells and multiplied the values by 2.
Filled the Cells so
Then I ran this code
Please Login or Register to view this content.
On several runs it took 0.36 seconds/runPlease Login or Register to view this content.
Are you using worksheet formulas like transpose on your array? They could give that symptom, don’t use them
Redim Preserve is very resource exhaustive, espescially on big arrays since the y have to be recalculated every time. It's to be avoided as much as possible.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks