Get array from filtered results

Alphacsulb

Active Member
Joined
Mar 20, 2008
Messages
414
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:
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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:
Upvote 0
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:

Excel 2013/2016
AB
1CITYno
2Conway1
3Springdale2
4Santa Clara3
5Jonesboro1
6Marana2
7Springdale3
8Tucson1
9Marana2
10Conway1
11Springdale2
12Tucson2
13Tucson3
14Jonesboro2
15Chula Vista2
16Chula Vista3
Sheet1


Autofilter (by col B with 1 as criteria):

Excel 2013
AB
1CITYno
2Conway1
5Jonesboro1
8Tucson1
10Conway1
Sheet1


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

Excel 2013
AB
1CITYno
2Conway1
5Jonesboro1
8Tucson1
10Conway1
12Tucson2
13Tucson3
14Jonesboro2
Sheet1
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

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