filter using a table list with VBA

trevorc · #1 11-07-2018, 12:52 PM

Hi All,
I am trying to improve on some code I have, currently the code below works fine, but if I need to add a condition to the list for the filter I also have to add it to this code. Is it possible to use a table list for the filter?
It also seems to have a problem with Capital letters - with/without requires 2 entries, can I also fix this other than with a list from a table?

Code:

Sub Filter_Closed()
t = ActiveSheet.Shapes("fltr").TextFrame.Characters.Text
     If ActiveSheet.Shapes("fltr").TextFrame.Characters.Text = "Filter Closed" Then
        ActiveSheet.ListObjects("Customers").Range.AutoFilter Field:=3, _
        Criteria1:=Array("Done, waiting for confirmation of payment", _
        "In Transit", "Received for Repair, Proceed with Repair", _
        "waiting delivery of parts", "Waiting for Email Address", "Waiting for reply from customer", "="), Operator _
        :=xlFilterValues
        ActiveSheet.Shapes("fltr").TextFrame.Characters.Text = "Show All"
      Exit Sub
      Else
        ActiveSheet.ListObjects("Customers").Range.AutoFilter Field:=3
        ActiveSheet.Shapes("fltr").TextFrame.Characters.Text = "Filter Closed"
      End If
End Sub

NoSparks · #2 11-10-2018, 09:11 AM

If not yet resolved...
have you tried using advanced filter and Option Compare Text ?

trevorc · #3 11-11-2018, 12:47 PM

Thanks,
I did try that but it failed to work correctly, even on a simple subset of data, I couldn't even tweak the VBA code to make it work after using the recorder to create the code, the recorded code didn't even work. I'll look at it again at a later date

NoSparks · #4 11-11-2018, 04:48 PM

Like this ?

trevorc · #5 11-11-2018, 04:57 PM

Hi Thanks for that,
I can see how it works, I just need to transfer it into my workbook.
regards
Trevor

trevorc · #6 11-12-2018, 12:41 PM

I found out why mine wasn't working, My headers didn't match and it filtered out everything. All good now.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Drop down list/ filter help with sharepoint	DB12345	Misc	0	10-25-2016 02:03 PM
Filter Mail Merge based on a list of filter criteria	AusSteelMan	Mail Merge	2	05-09-2016 03:35 PM
Data validation list filter with range defined by OFFSET	Mango123	Excel	4	03-18-2014 02:52 PM
filter according to a list of filters	userman	Excel	2	06-12-2012 02:19 AM
Growing Filter List	rbdmg	Mail Merge	1	10-10-2011 02:56 AM