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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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

S
  • Question
VBA To Filter Conditionally Formatted coloured cells in multiple columns
Replies
1
Views
224
bobsan42
bobsan42
airforceone
  • Solved
AutoFilter Working but not on Date
Replies
5
Views
720
Alex Blakenburg
A
M
  • Question
Table issue with VBA
Replies
2
Views
149
Mike2
M
Waving Not Drowning
  • Solved
Filter criteria range from two cells including in VBA macro
Replies
3
Views
185
Alex Blakenburg
A
D
  • Solved
Run-time error '1004': Application-defined or object-defined error
Replies
4
Views
517
rollis13
rollis13

Forum statistics

Threads
1,216,124
Messages
6,128,993
Members
449,480
Latest member
yesitisasport

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