Get array from filtered results

[Alphacsulb]

I'm attempting to capture an array from filtered results to then use later in a filter.

I tried following this logic but cant figure out how to change it to my needs since I'm only using one sheet.

https://contexturesblog.com/archives/2010/12/15/excel-autofilter-with-criteria-in-a-range/

Sub Executive_RSVP()
    
    Dim vCrit As Variant
    
    Sheets("EmailGroupSelection").Select ' using this sheet
    ActiveSheet.AutoFilterMode = False ' resets autofilter
    
    ActiveSheet.Range("$F$1:$F$1000").AutoFilter Field:=1, Criteria1:= _
        "=*Executive*", Operator:=xlAnd ' Filter Executive's (Column F) to figure out which agency they belong (Column A defines agency)
   
   ' This is where I need help capturing the array:
   ' Capture the non-hidden results from Column A skipping the header row and place as VCrit varient to use in the next filter below.
   
    Columns("A:F").Select
    Selection.AutoFilter
    ActiveSheet.Range("$F$1:$F$1000").AutoFilter Field:=6, Criteria1:= _
        "=*Executive*", Operator:=xlOr, Criteria2:="=*RSVP*" ' Filtered list contains Executives or staff who perform RSVP.
    ActiveSheet.Range("$A$1:$A$1000").AutoFilter Field:=1, _
    Criteria1:=Application.Transpose(vCrit), _
    Operator:=xlFilterValues 'Use the vCrit array to only List RSVP staff if the executive belongs to the agency.


Do Until Application.CalculationState = xlDone
   DoEvents
 Loop
 
End Sub

I hope that is clear.

Thanks for any leads.

 

[MoshiM]

I'm attempting to capture an array from filtered results to then use later in a filter.

I tried following this logic but cant figure out how to change it to my needs since I'm only using one sheet.

https://contexturesblog.com/archives/2010/12/15/excel-autofilter-with-criteria-in-a-range/

I hope that is clear.

Thanks for any leads.

I'm sure there's a simplier way to do this but try the following

vcrit=Return_Filtered_From_A("[COLOR=#333333]EmailGroupSelection")[/COLOR]

Function Return_Filtered_From_A(Worksheet_Name As String) As Variant


Dim Target_Sheet As Worksheet, AR As Range, vcrit_1 As Variant, vcrit_temp As Variant, Visible_Range As Range, _
EB As Boolean


Set Target_Sheet = ThisWorkbook.Sheets(Worksheet_Name)


Set Visible_Range = Target_Sheet.UsedRange.Columns("A").SpecialCells(xlCellTypeVisible)
    
With Visible_Range


    '.Sort .Cells(2, 1), xlAscending, Header:=xlYes'sort into groups for
    
    ReDim vcrit_1(1 To Target_Sheet.UsedRange.Rows.count) '1D array
        
    y1 = 1
    
    For Each AR In .Areas
        
        EB = False
        
        If Intersect(AR, Target_Sheet.Rows(1)) Is Nothing Then 'if the area doesn't intersect the headers
            
            vcrit_temp = AR.Value
            
            EB = True
        
        ElseIf Not Intersect(AR, Target_Sheet.Rows(1)) Is Nothing And AR.Rows.count > 1 Then
            'if header intersects the area and it isn't the only row in the current area then resize to exclude it
            vcrit_temp = AR.Cells(1, 1).Offset(1, 0).Resize(AR.Rows.count - 1, 1).Value
            
            EB = True


        End If
            
        If EB = True And AR.Cells.count > 1 Then 'if more than one cell and the [Enter Boolean] is true
            
            For y2 = 1 To UBound(vcrit_temp, 1) 'loop through values of the array
                
                vcrit_1(y1) = vcrit_temp(y2, 1) 'complile into singular array
                
                y1 = y1 + 1
                
            Next y2
        
        ElseIf EB = True And AR.Cells.count = 1 Then
            
            vcrit_1(y1) = vcrit_temp
            y1 = y1 + 1
            
        End If
        
    Next AR
    
End With


If IsEmpty(vcrit(UBound(vcrit_1))) Then 'if the last item in the array is empty
    
     y1 = UBound(vcrit_1)
    
    Do Until IsEmpty(vcrit_1(y1)) = False Or y1 = 1 ' remove empty array values
         y1 = y1 - 1
    Loop
    
    ReDim Preserve vcrit_1(1 To y1)
    
End If


Return_Filtered_From_A = vcrit_1


End Function

 

[Akuini]

You can use dictionary object as an array criteria.
Here's an example:

[FONT=Lucida Console][color=Royalblue]Sub[/color] a1112343a()
[i][color=Dimgray]'https://www.mrexcel.com/forum/excel-questions/1112343-get-array-filtered-results.html[/color][/i]
[color=Royalblue]Dim[/color] c [color=Royalblue]As[/color] Range
[color=Royalblue]Dim[/color] d [color=Royalblue]As[/color] [color=Royalblue]Object[/color]

ActiveSheet.AutoFilterMode = False
[color=Royalblue]Set[/color] d = CreateObject([color=Darkcyan]"scripting.dictionary"[/color])
d.CompareMode = vbTextCompare

[color=Royalblue]With[/color] Range([color=Darkcyan]"A1:A16"[/color])

    .AutoFilter Field:=[color=Brown]2[/color], Criteria1:=[color=Brown]1[/color]
        [color=Royalblue]For[/color] [color=Royalblue]Each[/color] c In .Columns([color=Brown]1[/color]).Offset([color=Brown]1[/color]).SpecialCells(xlCellTypeVisible)
            [color=Royalblue]If[/color] [color=Royalblue]Not[/color] c = [color=Darkcyan]""[/color] [color=Royalblue]Then[/color] d(c.Value) = [color=Royalblue]Empty[/color]
        [color=Royalblue]Next[/color]
    .AutoFilter
    .AutoFilter Field:=[color=Brown]1[/color], Criteria1:=d.Keys, Operator:=xlFilterValues

[color=Royalblue]End[/color] [color=Royalblue]With[/color]

[color=Royalblue]End[/color] [color=Royalblue]Sub[/color][/FONT]

Data:

Excel 2013/2016
	A	B
1	CITY	no
2	Conway	1
3	Springdale	2
4	Santa Clara	3
5	Jonesboro	1
6	Marana	2
7	Springdale	3
8	Tucson	1
9	Marana	2
10	Conway	1
11	Springdale	2
12	Tucson	2
13	Tucson	3
14	Jonesboro	2
15	Chula Vista	2
16	Chula Vista	3
Sheet1

Autofilter (by col B with 1 as criteria):

Excel 2013
	A	B
1	CITY	no
2	Conway	1
5	Jonesboro	1
8	Tucson	1
10	Conway	1
Sheet1

Autofilter (by col A with d.keys as array criteria):

Excel 2013
	A	B
1	CITY	no
2	Conway	1
5	Jonesboro	1
8	Tucson	1
10	Conway	1
12	Tucson	2
13	Tucson	3
14	Jonesboro	2
Sheet1