Autofilter and Criteria Help Vba

  • Hello guys, was wondering if anyone would be able to help me out with the following code.

    Using some help from online guides, I have managed to create an autofilter to filter out keywords from a VERY long column with a lot of information. I have it set to when I hit a command button the filter begins and sorts out. it does work however when I get to the end of the coding window it seems to just break? and I'm not entirely sure on the why?

    would anyone be able to offer any assistance?

    here is roughly the code I'm using.


    Private Sub CommandButton1_Click()

    Sheet2.Range("A1:P1").AutoFilter Field:=7, _

    Criteria1:=Array("Apples", "Oranges", "Carrots", "Peas", "and like I said this works but it continues to go this way >>>>>>> very long and it breaks."), _


    End Sub

  • Here is a sample of the file, hopefully you can guide me in the right direction.


    • Sample.xlsm

      (238.12 kB, downloaded 26 times, last: )
  • This code assumes that you have formatted the data as a Table.

    Overview of Excel Tables.

    I think you are trying to filter for all items containing KIT>

  • Thank you Roy,

    I did try your Code on my sheet, however it gave me an error on this line, Set Lst = Sheet2.ListObjects(1).

    but you did give me an idea to keep using the code that I had been using this one.

    1. Private Sub CommandButton1_Click()
    2. Sheet2.Range("A1:P1").AutoFilter Field:=7, _
    3. Criteria1:=Array("*KIT*"), _
    4. Operator:=xlFilterValues
    5. End Sub

    And that does seem to be filtering exactly what I would like.

    Now i would just need a code to use with a command button to clear autofilters, Still working on that sense it does not appear to be working, If you could provide some help with that I would appreciate it.

  • I'm still struggling to get this command button to clear my filters from a column. this is what I have. However it is giving me an

    "Expected End Sub" Could anyone help me out?

    1. Private Sub CommandButton2_Click()
    2. Sub Clear_Column_Filter_Range()
    3. 'To clear the filter from a Single Column, specify the
    4. 'Field number only and no other parameters
    5. Sheet3.Range("G3:G1802").AutoFilter Field:=7
    6. End Sub