Populate array with large filtered range

[Journeyman3000]

Hi all.

There has got to be an easy way to do this. I am trying to populate a large sheet with many columns and many rows into an array.

The caveat is that the rows are filtered.

Assuming declarations...

Please Login or Register  to view this content.

I would rather not loop xlCellTypeVisible and take forever to loop every row and every column value into an array.

Is there an easier way to populate an array with filtered rows?


Cheers

[vba_php]

hmmmm....I bet there is an expert here that knows. have you tried selecting the entire range while recording the movements with the macro recorder? VBA has a function in it called SPLIT() but i don't think that would because you have to have a delimiter in the arguments. what about answer #2 here?

https://stackoverflow.com/questions/...a-range-in-vba

or simply, to get overloaded with options:

https://www.google.com/search?q=vba+...range+to+array

[Journeyman3000]

Thanks for the response.

Answer Number 2 in your suggested page works for a single dimension array, which is not filtered. I need my array to collect the detail from a filtered sheet.

Using the Macros recorder produces Copy and Paste Selection Code, which doesn't work to paste into an array.

However, I appreciate the time taken to provide a response.

Cheers

[vba_php]

Answer Number 2 in your suggested page works for a single dimension array, which is not filtered. I need my array to collect the detail from a filtered sheet.

so do you need a one-dim output or a 2-dim output? here is 1:

Please Login or Register  to view this content.

and putting the data into a 2 dimensional array should be just as easy. but based on what you asked for, I'm not sure why you would want to do that, because every data point (value) represents a column and a row already.

[jindon]

I would rather not loop xlCellTypeVisible and take forever to loop every row and every column value into an array.

Is there an easier way to populate an array with filtered rows?

Prepare hidden sheet and copy filtered data to a hidden sheet and get data from there without loop.

[romperstomper]

You can also sort the source sheet on the filter columns so that you end up with as few discrete areas as possible when filtering, then loop through the areas of the visible cells, load each area into an array and then iterate that populating your master array.

[Rick Rothstein]

I think this may be the non-looping concept jindon proposed (although he may have had different coding in mind) except that I chose to copy to the active sheet (which I am assuming it the sheet with the filtered data), load the array and then delete the copied data...

Please Login or Register  to view this content.

[Journeyman3000]

@Rick RothStein

This solution appears to be exactly what I'm looking for.

However, I am wondering if there is a way to do this in code (Memory) rather than creating a Temporary sheet.

If I had to create a temporary sheet, I would have to make it invisible - or extra hidden then have to deal with the issues related to that.

Otherwise, this is excellent. Thank you for your suggestion.

Cheers

[Rick Rothstein]

I did not create a temporary sheet... I simply copied the data into an unused area under the existing data, loaded it into an array and then deleted what I had copied. Everything is taking place on the active sheet which I assumed is where your data is located... no extra or hidden sheets are required with the code snippet I posted.

[Journeyman3000]

I can't mess with the original data sheet. That's why I modified your code to use a temp sheet.

I tested your code to make sure it worked and my philosophy is always to 'Make it work before I make it pretty'

[Rick Rothstein]

I can't mess with the original data sheet.

Even if that "messing" takes place in an unused area of the sheet? What if we created a workbook on the fly, copied the data and then retrieved it from there and then closed that temporary workbook... would that approach be acceptable?

However, I am wondering if there is a way to do this in code (Memory) rather than creating a Temporary sheet.

I guess we could loop through the cells of the visible cells and write the data to the array one cell at a time. Not sure how inefficient that might be though. Did you want to pursue that method?


You might also want to wait for jindon to come back to this thread to see if he has any ideas on how to do this wholly (and efficiently) in memory.

[Journeyman3000]

Hi Rick

I guess we could loop through the cells of the visible cells and write the data to the array one cell at a time. Not sure how inefficient that might be though. Did you want to pursue that method?

Absolutely not. This entire exercise is to avoid looping through millions of cells.

If I have no choice but to create a temporary sheet then I will have to live with it. I've made it work based on your input.

My end goal is to make an array of only visible rows with non-concurrent columns (e.g. B:B, H:H,Z:Z). But for all my research I've not found an adequate solution to this problem, except by looping, which bites. Makes me cry.

Thanks for help.

Cheers

[jindon]

If you know the filtered criteria/column, it could be done.

[Journeyman3000]

I tried this:

Please Login or Register  to view this content.

But no happiness.

My guess is I would have to loop through the temp sheet and remove the columns I don't need. more ugliness, but what else can I do.

Cheers

[jindon]

Please Login or Register  to view this content.

[Journeyman3000]

Mate!

Aside from the requirement to use a helper sheet, this is fantastic. I even set the numbering to see if it auto ordered the sheets, which it does.

Excellent. Thank you for this.

[jindon]

I said

If you know the filtered criteria/column, it could be done.

Means if you know the criteria, it could all be done within a memory. No helper sheet.

[Journeyman3000]

I used the helper sheet to avoid using the original sheet as suggested

This is the code

Please Login or Register  to view this content.

I tried making the varVisibleArray from the origin sheet, but I couldn't get it to work

[Peter_SSs]

I used the helper sheet to avoid using the original sheet as suggested

I tried making the varVisibleArray from the origin sheet, but I couldn't get it to work

If I have understood correctly then I don't think that you need a helper sheet and I don't think you even need to get the visible cells into an array as they can be transferred directly to the destination worksheet. My suggestion does have a double loop but it it looping within memory only and only relating to the visible rows in one column so I don't think you will find a performance problem.

Give this a try with a copy of your workbook

Please Login or Register  to view this content.

[Journeyman3000]

Thank you so much Peter (And Jindon), and all other who responded to this challenge.

This works very well, and is a ton faster than Before.

I will now close this issue as this question is answered (so very brilliantly!)

Thank you.

[Peter_SSs]

Thank you so much Peter (And Jindon), and all other who responded to this challenge.

This works very well, and is a ton faster than Before.

You're welcome. Glad we could help.