VBA code to apply filter based on multiple selection

S

somersault

Board Regular
Joined
Jun 24, 2008
Messages
59
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??
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
Have you tried passing the ranges to an array and using that?

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

Code: 
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.
 
Upvote 0
Andrew Poulsom said:
Tested in Excel 2007:

Code: 
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.
Click to expand...
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Solved
Formatting a filter command.
Replies
2
Views
50
USAMax
U
J
  • Solved
Search/Filter a Spreadsheet by User Entered Value
Replies
3
Views
201
jbennett01
J
S
  • Solved
How to filter and change text in Macro
Replies
2
Views
250
SCPbrito
S
C
  • Locked
  • Solved
select a cell in a filtered row
Replies
3
Views
100
Peter_SSs
Peter_SSs
I
  • Question Question
Filter column on text value if that value exists using VBA
Replies
7
Views
386
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,599
Messages
6,179,827
Members
452,946
Latest member
JoseDavid

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top