VBA : Autofilter results copied to Array

[Paluee]

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

[mdbct]

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

Sub GetMeterCode()

Dim ws As Worksheet
Dim val As Integer, LR As Integer, rowsTotal As Integer
Dim rngArray(), i As Long

Dim rngTemp As Range

Set ws = Workbooks("ChooseMeterCode_Ver02.xls").Worksheets("first")
val = UserForm1.txtb1.Value

MsgBox " The export code value is : " & val

ws.Range("A5:C5").Select
Selection.AutoFilter
Selection.AutoFilter Field:=3, Criteria1:="=" & val
LR = ws.Range("A" & Rows.Count).End(xlUp).Row
'Set rngTemp = ws.Range("A5:C5").AutoFilter(Field:=3, Criteria1:="=" & val)
rowsTotal = ws.Range("A5:A" & LR).SpecialCells(xlCellTypeVisible).Count - 1

ReDim rngArray(1)
MsgBox "The filtered row numbers are: " & rowsTotal

For i = 6 To LR
If Not Cells(i, "A").EntireRow.Hidden Then
ReDim Preserve rngArray(UBound(rngArray) + 1)
    rngArray(UBound(rngArray)) = Cells(i, "A")
End If
Next

 For i = 2 To UBound(rngArray)
    MsgBox "Values from array are : " & rngArray(i)
 Next i

ws.AutoFilterMode = False

End Sub

[Paluee]

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

[mdbct]

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