VBA: Set an Array equal to filter criteria

Question

Currently I have data that is already filtered business type. I want to fill an array with the values from another column without any repeats. In other terms I want to fill an array with the filter criteria from another column.

The filter criteria in the other column will change depending on what business type is selected so filling the array needs to be dynamic.

I've researched this online and so far have only found this which doesn't work:

Dim tempArr As Variant

tempArr = Sheets("Sheet1").Filters.Criteria1

Sample Data

buisUnit   ProfCenter
SHS        1
SHS        1
SHS        2
SHS        3
SHS        4
ALT        5
ALT        6
ALT        6
ALT        7

So if my data is filtered on buis unit = SHS I would want tempArray = (1,2,3,4) if filtered on ALT i would want (5,6,7)

Thanks in advance.

Please show sample data so that it will be easily visualized. — Wils Mils
– Wils Mils, Commented Jul 13, 2020 at 12:36
Worksheets do not have a Filters property. They have AutoFilter.Filters but we need to see your code before we can make suggestions. Another thing, the s at the end of Filters indicates a collection, which means you need to use an index to access a certain filter (Filters(1) and so on) — Super Symmetry
– Super Symmetry, Commented Jul 13, 2020 at 12:42
What "without any repeats" does mean? Do you want saying unique values? — FaneDuru
– FaneDuru, Commented Jul 13, 2020 at 12:50

FaneDuru · Accepted Answer · 2020-07-13 13:40:45Z

1

Use the next function, please. It needs a reference to 'Microsoft Scripting Runtime'. If you cannot add it (even if it is extremely simple), just comment the first declaration line and un-comment the second one:

Function FilterArray(arr As Variant, strSearch As String) As Variant
    Dim dict As New Scripting.Dictionary
    'Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    Dim i As Long, strKey As String
    For i = 1 To UBound(arr)
      If arr(i, 1) = strSearch Then
        strKey = arr(i, 1) & "|" & arr(i, 2)
        If Not dict.Exists(strKey) Then
           dict.Add strKey, arr(i, 2)
        End If
      End If
  Next i
   If dict.Count = 0 Then FilterArray = "": Exit Function
   FilterArray = dict.Items
End Function

It would be called in the next way:

Sub testFilterArr()
  Dim sh As Worksheet, arr As Variant, strSearch As String, lastRow As Long
  
   Set sh = ActiveSheet 'use here the necessary sheet
   lastRow = sh.Range("A" & Rows.Count).End(xlUp).Row
   arr = sh.Range("A2:B" & lastRow).Value
   strSearch = "SHS"
   arr = FilterArray(arr, strSearch)
   If IsArray(arr) Then
       Debug.Print Join(arr, ",")
   Else
       MsgBox "No any result for """ & strSearch & """..."
   End If
End Sub

edited Jul 13, 2020 at 13:40

answered Jul 13, 2020 at 13:16

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

FaneDuru Over a year ago

@Wils Mils: Glad I could help! Important would be that the one asking the question will test it, too... :)

Collectives™ on Stack Overflow

VBA: Set an Array equal to filter criteria

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related