VBA code to apply filter based on multiple selection

[somersault]

Any idea why the below doesnt work in excel 2007??

Original code (as recorded by excel - this works well):
ActiveSheet.Range("$F$4:$G$18").AutoFilter Field:=2, Criteria1:=Array("1", "2", "3"), Operator:=xlFilterValues

Note that 1, 2, 3 are filter selection.

Dyanmic code (to make the filter selection dynamic)
r = sheets("sheet1").range("P2").value
ra = sheets("sheet1").range("P3").value
rb = sheets("sheet1").range("P4").value

ActiveSheet.Range("$F$4:$G$18").AutoFilter Field:=2, Criteria1:=Array(r, ra, rb), Operator:=xlFilterValues

i have defined the filter selections in excel range, and refered to them.

I am unable to find out my mistake, could you help??

 

[Andrew Poulsom]

Have you tried passing the ranges to an array and using that?

Dim Arr As Variant
Arr = Worksheets("Sheet1").Range("P2:P4").Value
ActiveSheet.Range("$F$4:$G$18").AutoFilter Field:=2, Criteria1:=Arr, Operator:=xlFilterValues

 

[somersault]

I tried this, but this is still not working.

 

[Andrew Poulsom]

I can't test but maybe try:

Arr = WorksheetFunction.Transpose(Worksheets("Sheet1").Range("P2:P4").Value)

 

[Andrew Poulsom]

Tested in Excel 2007:

Sub Test()
    Dim Arr As Variant
    Dim i As Integer
    Arr = WorksheetFunction.Transpose(Worksheets("Sheet1").Range("P2:P4").Value)
    For i = LBound(Arr) To UBound(Arr)
        Arr(i) = CStr(Arr(i))
    Next i
    ActiveSheet.Range("$F$4:$G$18").AutoFilter Field:=2, Criteria1:=Arr, Operator:=xlFilterValues
End Sub

If the criteria are numbers they need to be converted to strings.

 

[somersault]

Thanks..

This works..

Cheers...

 

[dixonlv]

Tested in Excel 2007:

Sub Test()
    Dim Arr As Variant
    Dim i As Integer
    Arr = WorksheetFunction.Transpose(Worksheets("Sheet1").Range("P2:P4").Value)
    For i = LBound(Arr) To UBound(Arr)
        Arr(i) = CStr(Arr(i))
    Next i
    ActiveSheet.Range("$F$4:$G$18").AutoFilter Field:=2, Criteria1:=Arr, Operator:=xlFilterValues
End Sub

If the criteria are numbers they need to be converted to strings.

Is it possible to populate an array (without looping) with the filtered data?
Something similar to:
Dim arFiltArray as Variant
arFiltArray = ActiveSheet.Range("$F$4:$G$18").AutoFilter Field:=2, Criteria1:=Arr, Operator:=xlFilterValues

 

[Andrew Poulsom]

No, only a contiguous range can be transferred to a Variant array. Even if it were possible your code wouldn't work because the AutoFilter method doesn't return a Range object.

 

[dixonlv]

Thanks for the quick reply.
Perhaps there is another method for what I'm trying to accomplish.
I would like an array created from the filtered Range data.
I see some things that lead me to believe that this or something similar may be possible. (Thinking way outside the box).
With the filtered data, I can select the visible range and copy into memory via the clipboard.
Would it be possible to use the split function to create the array from the clipboard?
I tried using the split function with vbLF as the delimiter, but this creates an array element for each row.
With the help of your code above, I think I'm very close.


Sub AutoFilter()
'Les Dixon 2010-04-26
'Adapted from Andrw Poulsom
'http://www.mrexcel.com/forum/newreply.php?do=newreply&p=2292663
'Create Array From AutoFilter Function
'Required:
'Table to sort in Column A - L (Change if needed)
'Sort Value List in Column P4-Px (Change if needed)

Dim arSG As Variant
Dim arTest As Variant
Dim x As Integer
Dim strActWS As String
Dim DataObj As New MSForms.DataObject
'Get ActiveSheet name
strActWS = ActiveSheet.Name
'Clear Filter
Sheets(strActWS).AutoFilterMode = False
'Get Sort Value List Last Row (defined in col p, change if needed)
sinLSGRow = Sheets(strActWS).Range("P65336").End(xlUp).Row
'Create Array from Sort Value List (defined in col p, change if needed)
arSG = WorksheetFunction.Transpose(Worksheets(strActWS).Range("P4:P" & sinLSGRow).Value)
'Get Data Set last Row
sinLLRow = Sheets(strActWS).Range("E65336").End(xlUp).Row
'Filter Data
Sheets(strActWS).Range("$A$3:$L$" & sinLLRow).AutoFilter Field:=6, Criteria1:=arSG, Operator:=xlFilterValues


'**********************************************************************************************
'Testing to create array from filtered data
'Select Filtered Data & Copy
Range("A4:L" & sinLLRow).Select
arTest2 = Selection.Copy

'Create Array from Clip board (Set reference to Microsoft Form 2.0 Object Library)
DataObj.GetFromClipboard

'The data is captured I just need a solid method to create a
'Multi-dimensional array from the clipboard data
arTest = Split(DataObj.GetText, vbLf)
​

End Sub

 

[Andrew Poulsom]

Copy the visible cells to blank area or a new worksheet. Then transfer the pasted range to a variant array.

http://www.dailydoseofexcel.com/archives/2010/03/08/getting-array-data-from-a-filtered-list-in-vba/