Get array from filtered results

Alphacsulb

Active Member
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/

Code:
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. :eek:
 

MoshiM

Active Member
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. :eek:
I'm sure there's a simplier way to do this but try the following

Code:
vcrit=Return_Filtered_From_A("[COLOR=#333333]EmailGroupSelection")[/COLOR]
Code:
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
 
Last edited:

Akuini

Well-known Member
You can use dictionary object as an array criteria.
Here's an example:

Code:
[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:
<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">CITY</td><td style=";">no</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Conway</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Springdale</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Santa Clara</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Jonesboro</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Marana</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Springdale</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Tucson</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Marana</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Conway</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Springdale</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Tucson</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Tucson</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Jonesboro</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Chula Vista</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">Chula Vista</td><td style="text-align: right;;">3</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br />

Autofilter (by col B with 1 as criteria):
<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(222,252,236)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(222,252,236);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">CITY</td><td style=";">no</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Conway</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Jonesboro</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Tucson</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Conway</td><td style="text-align: right;;">1</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(222,252,236);color: rgb(22,17,32)">Sheet1</p><br /><br />

Autofilter (by col A with d.keys as array criteria):
<b>Excel 2013</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(222,252,236)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(222,252,236);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">CITY</td><td style=";">no</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Conway</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Jonesboro</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Tucson</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Conway</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Tucson</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Tucson</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Jonesboro</td><td style="text-align: right;;">2</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(222,252,236);color: rgb(22,17,32)">Sheet1</p><br /><br />
 

Some videos you may like

This Week's Hot Topics

Top