Making a Search Bar with VBA?

[djamies1]

Hi Mr. Excel forums!

I am trying to make a search option with one of my excel workbooks. The user is supposed to type a search term into cell X12 and then click the search button (active x control). This prompts the code below to search through all the cells with data in column A and hide any rows which do not contain the user entered search term.

The code below works correctly, however it runs very slowly and isn't practical for users. It is so slow because it is cycling through each row and hiding/showing them one at a time. If anyone has any ideas for making this run more efficiently and quicker I'd really appreciate it!

One idea I had was to assign a value of hide or show to each row during the loop, then say something like...

With (all searchable rows)
Hide if RowValue = Hide
Show if RowValue = Show
End

I haven't been able to figure out a way to make the this idea work however...

Current working (but very slow) code:

Private Sub SEARCH_Click()


Dim ItemRow As Integer
Dim SearchBar As String


SearchBar = Range("X12")

'First searchable row
ItemRow = 18

'758 is last searchable row
Do While ItemRow < 759

If InStr(1, Cells(ItemRow, 1), SearchBar) Then

Rows(ItemRow).Hidden = False

Else

Rows(ItemRow).Hidden = True

End If

ItemRow = ItemRow + 1


Loop

End Sub

 

[RickXL]

Hi, it looks like you might want to try an AutoFilter.

First, select the cell over the list you want to filter. Looking at your code, if you have a title in cell A17 then select that.
Then on the Home tab, select the Editing menu then choose "Sort & Filter".
Then select filter.

A dropdown arrow should appear. Click that to get to the filter options.

 

[L. Howard]

You could try something like this, with your active-x button named sub.

Howard

Option Explicit

Sub Your_Button_Click_Name()

Dim aSch As String
aSch = Range("B1")

    [A2].AutoFilter
    ActiveSheet.Range("$A$2:$B$22").AutoFilter Field:=1, Criteria1:=aSch, _
        Operator:=xlAnd
        
    Dim myCheck
    myCheck = MsgBox("Return column A un-FILTERED", vbYesNo)
    If myCheck = vbNo Then
        Exit Sub
       'Clicked No.
    Else
       Selection.AutoFilter
        'Clicked Yes
    End If
        
End Sub

 

[L. Howard]

Change:

aSch = Range("B1")

To
aSch = Range("X12")

Howard