VBA to Advance Filter

[gheyman]

Can you use VBA to run an Advance Filter function?

I want to filter to to value in D8 and I want to filter my table "Vendor_List[Vendor]"

But I want to include "*" before and after the value the D8

Range("Vendor_List[Vendor]").AdvancedFilter Action:=xlFilterInPlace, Unique _
:=False

(I dont want to copy the results, I only want to filter my table)

Can this be done?

Thanks for the help!

 

[Peter_SSs]

Does this do what you want?
I have assumed Z1:Z2 can be used as helper cells.

Sub AdvFltrTbl()
  Dim rCrit As Range
  
  Set rCrit = Range("Z1:Z2")
  rCrit.Cells(2).Formula = "=ISNUMBER(Search(""" & Range("D8").Value & """," & Range("Vendor_List[Vendor]").Cells(1).Address(0, 0) & "))"
  Range("Vendor_List[#All]").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rCrit, Unique:=False
  rCrit.ClearContents
End Sub

 

[Peter_SSs]

I want to filter to to value in D8 and I want to filter my table "Vendor_List[Vendor]"

But I want to include "*" before and after the value the D8

.. but I'm wondering why Advanced Filter? Why not AutoFilter?

Sub AutoFltrTbl()
  With ActiveSheet.ListObjects("Vendor_List")
    .Range.AutoFilter Field:=.ListColumns("Vendor").Index, Criteria1:="*" & Range("D8").Value & "*"
  End With
End Sub

 

[Alex Blakenburg]

@Peter_SSs I was about to suggest Autofilter but was busy googling your advanced filter formula method. There is another thread out there at the moment for which that would work really well.
Thanks for posting that.
PS: A good reference for the formula method is Paul Kelly's article here: VBA Advanced Filter: A Complete Guide - Excel Macro Mastery
Sub Heading "Using Formulas as Criteria"

 

[gheyman]

Thanks!

 

[Peter_SSs]

You're welcome.