Create a button in excel that filters a specific text within a column

Question

I am trying to create a button in excel that filters Column E. Column E is named Category (on-site, Email, Phone). I want to create three buttons one for On-site, one for Email and another one for Phone.

For example, if I click on On-site, it should show me all on-site questions and same goes for other two buttons.

Sub Button1_Click() 
    Range("E:E").AutoFilter Field:=1, Criteria1:="phone" 
End Sub

With this code, I only see records with Phone and not the ones that are both On-Site and Phone.

Also, it doesn't show me all the questions that are categorized under pre-phone. If there are 20 questions, it filters only 10.

Thanks!

So what's your question? Please know that "How do I do this?" or "Where do I start?" are too broad for this forum. This forum is for those with existing code that does not work to get help overcoming the specific problem with said code. — Scott Craner, Aug 11 '16 at 13:43
Not sure exactly what you are looking for, but maybe a slicer might help? It filters data based on criteria. Unfortunately it only works with Pivot Tables. Check out [Chandoo](http://chandoo.org/wp/2015/06/24/introduction-to-slicers/) and [Microsoft](https://msdn.microsoft.com/en-us/library/gg399135(v=sql.110).aspx) to learn about them. — PartyHatPanda, Aug 11 '16 at 13:47
Sub Button1_Click() Range("E:E").AutoFilter Field:=1, Criteria1:="Pre-phone" End Sub This is what I coded for one of the buttons but It doesn't seem to work. — sjb12, Aug 11 '16 at 13:52
I am not understanding your second comment **"With this code, I only see records with Phone and not the ones that are both On-Site and Phone."** If you want it filtered to phone, why are you expecting On-Site information? Is there a way to see the data you are using to get a better understanding of what you are looking for? — PartyHatPanda, Aug 11 '16 at 14:27
I am sorry about the confusion. There are some rows which includes the Phone and On-Site type both. I want to be able to filter every row that has a value phone in there. Doesn't matter if it has Phone, On-site and email all in one. It should still be able to pull that record as it has Phone in there. — sjb12, Aug 11 '16 at 14:36

score 0 · Answer 1 · answered Aug 11 '16 at 14:39

This might be helpful. You can write out three macros with this format, changing the 'criteria' variable in each based on what you want to filter for. This will include cells with multiple entries. Then, you can insert three buttons (tutorial here) where each one links to their own macro. Make sure to change the macro name for each of them!

Sub ManualFilter()

Dim criteria As String

'What to look for
criteria = "Email"

'Where to look (Column E)
Dim myRange As Range
Set myRange = Range("E2", Cells(Rows.Count, "E").End(xlUp))

Dim myCell As Range

'Compare with criteria
For Each myCell In myRange
    'if it matches:
    If InStr(1, myCell.Value, criteria) <> 0 Then
        myCell.EntireRow.Hidden = False
    Else
        'if it doesn't match:
        myCell.EntireRow.Hidden = True
    End If
Next myCell

End Sub

Create a button in excel that filters a specific text within a column

1 Answers1