VBA : Autofilter results copied to Array

Hi there,

I need help in putting the results of Autofilter into an Array.

Attached is my VBA application. See the Code.

The example goes like this, I have a block of 20 rows, with 3 columns.
There is a header row.

I input into a text box one of two integer values that correspond to values in column 3.
The values are either 1 or 2.
It then filters to find all rows that have that value, this is very much like a lookup.
I am able to get the filtered results row count.
So the number of rows that have a "1" in column 3, there are 7 of them.

I tried to put the filter results in an array and display with message box field#1, but only returns the first 3 rows, meaning that it will return only contiguous cells that have value
one.

Help with this is much much appreciated.
See my attached VBA app.

Sincerely,

Paluee

I think you'll have to load the array using a loop:

Please Login or Register  to view this content.

Hi there,

Yes I actually came up with a very similar type of code myself.
But I was wondering if there was a way to get the resultset from the Autofilter itself.
SO I guess my question is:

"Can AutoFilter pass back a resultset or an Array with the results?"

Is there some method/function that it has that can do this so that one can avoid
looping thru a range and using conditional IF statements.

Thanks for your solution, it is more compact than my solution(in the looping way).

Thanks again.

Sincerely,

Paluee

"Can AutoFilter pass back a resultset or an Array with the results?"
As far as i can tell - no.