Create an array for filtered and non-contiguous columns

Hello everyone
I have in my file filtered data based on column G

I need to create an array of the values of these filtered data for two columns (B & G) only

In other word to store the values of these two filtered columns (visible rows only) not all the values

Hi,

I gave you what you asked for, but also added what you probably need instead:
a. You asked for a two dimensional array
b. I think you need a one dimensional data structure which contains the two columns.

Notice that the array is rather cryptic, because you have to rely on the index numbers to determine whether you have an Item or a Mount (Amount). With the data structure you know right away. Of course, you could declare constants for each of the array indices that indicate 'Item' or 'Mount.

The difference is (simplified non-working examples)

Please Login or Register  to view this content.

Actual working code in an ordinary code module such as Module1:

Please Login or Register  to view this content.

Lewis

Thanks a lot mr. Lewis for this perfect way ..
It is excellent but I didn't imagine it would be so long way.
The two ways are wonderful ..

Is there an easy way to create UDF function that deal with arrays and we as beginner users use the UDF to have the results quickly?
I imagine this : CreateFilteredArray(WholeRangeOfData,Column B,Column G)
=CreateFilteredArray(Range("A4:G92"),2,7)

Thanks for your great and awesome help

@ cyiangou
Amazing UDF fuction. I consider it very useful too that can save time and effort
Thanks a lot for this great and wonderful gift

Just a little modification if possible ... the first parameter to be more spefici that's :
Worksheets("Sheet1").range("A3:G3")

Please Login or Register  to view this content.

Range("A3:G3") is the title header of the filtered range
Thanks alot for this great help

I noticed that there is a reference "Microsoft Forms 2.0 Object Library" and I tried to check it to activate it in my file but I didn't find the reference.
I'm using office 2013

It works well with your attachment
After copying the code to my file ,I got error "user-defined type not defined" Compile error
Any idea about that

I clicked Browse to add the reference and browse in system32 folder for FM20.DLL and add it
I get rid of the error "User-Defined type not defined"

After that I tested the code again and I got the following error

Please Login or Register  to view this content.

Object variable or with block variable not set

Any idea

Thanks .. I could fix it ..
I just need a little modification >> Just to add a line that checks auto filter mode .. if it is true then the code to be executed but if it is false to exit sub

Thanks everyone
I have added this line to solve the problem

Please Login or Register  to view this content.

Thanks a lot for any help me even if with a simple idea

Thanks for the rep points YasserKhalil.

YasserKhalil wrote:
Is there an easy way to create UDF function that deal with arrays and we as beginner users use the UDF to have the results quickly? I imagine this : CreateFilteredArray(WholeRangeOfData,Column B,Column G)
=CreateFilteredArray(Range("A4:G92"),2,7)

The function you want can not be used in a formula (see the next post for reasons):

Please Login or Register  to view this content.

Try the following function (a slight modification of function CreateStructureArrayOfFilteredData() in post #2 above, which can be used in other VBA code. All the code that follows can be tested using the data from the file in post #2 above.

Please Login or Register  to view this content.

The following are a few ways to test the above function.

Please Login or Register  to view this content.

Lewis

Just FYI, you can late bind that function from cyiangou so you don't need the MS Forms reference each time:

Please Login or Register  to view this content.

Just for completeness, it is possible to work around b, c and d and enable a UDF to do pretty much anything a Sub can do, but it's complicated (either requiring Windows timers or using event code as well) and not usually a good idea.