VBA Arrays

[maslam15]

Hello,
I am using VBA arrays to select certain options in the list.
For Example the Code below Selects five people in the table. What I want to do with the array is to select everything excluding the options already selected. So instead of selecting these five people, select everything else in the column.

Range("Table1[[#Headers],[People]]").Select
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _
Array("Person 14", "Person 10", "Person 3", "Person 4", "Person 5"), Operator:= _
xlFilterValues

Thank you,

 

[DanteAmor]

Welcome to the forum!

Try this

Sub Filter_Arrays()
  Dim arr() As Variant, arr1() As Variant, sh As Worksheet, c As Range, j As Long, existe As Boolean
  Set sh = ActiveSheet
  sh.ListObjects("Table1").Range.AutoFilter Field:=1
  ReDim arr(1 To sh.ListObjects("Table1").Range.Rows.Count)
[COLOR=#0000ff]  arr1 = Array("Person 14", "Person 10", "Person 3", "Person 4", "Person 5") [/COLOR][COLOR=#008000] 'Put here the people who will be out of the filter.[/COLOR]
  For Each c In sh.ListObjects("Table1").ListColumns(1).DataBodyRange
    existe = False
    For j = 0 To UBound(arr1)
      If arr1(j) = c.Value Then existe = True
    Next
    If existe = False Then arr(c.Row) = c.Value
  Next
  sh.ListObjects("Table1").Range.AutoFilter 1, arr, xlFilterValues
End Sub

 

[steve the fish]

Bit long winded but should work:

arrFilterOut = Array("Person 14", "Person 10", "Person 3", "Person 4", "Person 5")
arr = ActiveSheet.ListObjects("Table1").ListColumns(1).DataBodyRange
With CreateObject("Scripting.Dictionary")
    For x = LBound(arr) To UBound(arr)
        If Not IsMissing(arr(x, 1)) And Len(arr(x, 1)) > 0 And IsError(Application.Match(arr(x, 1), arrFilterOut, 0)) Then
            .Item(arr(x, 1)) = 1
        End If
    Next
    arr = .keys
End With
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=arr, Operator:=xlFilterValues

 

[maslam15]

Thank you so much It works for me. I have one more question.
In my array list I also have blanks that I want to filter out

 

[DanteAmor]

Do you want blank cells to appear on the screen?

Then try this:

Sub Filter_Arrays()
  Dim arr() As Variant, arr1() As Variant, sh As Worksheet, c As Range, j As Long, existe As Boolean
  Set sh = ActiveSheet
  ReDim arr(1 To sh.ListObjects("Table1").Range.Rows.Count)
[COLOR=#ff0000]  arr(1) = ""[/COLOR]
  arr1 = Array("Person 14", "Person 10", "Person 3", "Person 4", "Person 5")
  For Each c In sh.ListObjects("Table1").ListColumns(1).DataBodyRange
    existe = False
    For j = 0 To UBound(arr1)
      If arr1(j) = c.Value Then existe = True
    Next
    If existe = False Then arr(c.Row) = c.Value
  Next
  sh.ListObjects("Table1").Range.AutoFilter 1, arr, xlFilterValues
End Sub

 

[maslam15]

Inside the column I am working in it has empty cells
I want to tell the array with the Array("Person 14", "Person 10", "Person 3", "Person 4", "Person 5")
also include blank cells because I want to filter out the blank cells also from what the options I select

Thank you,

 

[maslam15]

I figured it out I had to do this
Array("Person 14", "Person 10", "Person 3", "Person 4", "Person 5", " ")
add black space after last person
Thank you so much for your help I appreciate it

 

[maslam15]

My Apologies I meant Blank Space

 

[DanteAmor]

So it works for you?

 

[maslam15]

Yes it works for me
Thank you,