Deselect certain items in Drop Down list

[mocephur]

Hi Gurus,

I need some help with an excel workbook I have. I have a worksheet that is auto filtered for all columns, in column I is a list of business names, 2000 or more and growing. I would like to write a macro that "deselects" certain business names in column I from showing. What is a good way of doing this?

example:

Column I

Business Org Name
ABC Plumbing
Total Electric
Wrong Way
Right way
etc.

I'd like a macro that "deselects" Total Electric & Wrong Way from showing on the sheet. In actuality, I would be deselecting 27 business names for my needs.

Thanks!

 

[DanteAmor]

Change red data for your information

In column AY list the business names from row 1 and down.
Change T by the last column with data

Sub Macro()
    Dim aItems(), b As Range, c As Range, i As Long
    
    i = -1
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
    For Each c In Range("I2", Range("I" & Rows.Count).End(xlUp))
        Set b = Range("[B][COLOR=#ff0000]AY:AY[/COLOR][/B]").Find(c.Value, LookIn:=xlValues, lookat:=xlWhole)
        If b Is Nothing Then
            i = i + 1
            ReDim Preserve aItems(i)
            aItems(i) = c.Value
        End If
    Next
    ActiveSheet.Range("A1:[B][COLOR=#ff0000]T[/COLOR][/B]" & Range("I" & Rows.Count).End(xlUp).Row).AutoFilter Field:=9, Criteria1:=Array(aItems), Operator:=xlFilterValues
End Sub