Filter through a table, and display the results as you type

[dpaton05]

I have made a spreadsheet for work and I need to create a way to easily search through it, looking for data. I have used the new table feature in excel to record everything. I know you can select the down arrow at the right of the header and then you can unselect everything in the list then scroll through the list and pick what you want to appear from the column, press ok and it will show that row.

I want a simpler process than this. I would like a text box where you type what you are looking for and as you type, it searches through everything in that row and narrows the search down to rows that have text in them that satisfy the search criteria.

If this is possible, can someone show me how to do it, please?

 

[DanteAmor]

Works on 365.
Assuming you want to filter by the values you have in column A and the data is in the range A2 and up to F100.

In cell J2 put the following formula:

=FILTER(A2:F100,A2:A100=H1)

Insert a Textbox ActiveX Control

In LinkedCell properties of Textbox1 write H1

That's it, as you write in the textbox, you will automatically see the results in cell J2 onwards.

 

[dpaton05]

Thanks for that. Here is a image of the spreadsheet with some data in it. I have scrolled down to find an entry that has some extra lines entered. The names are blanked out for privacy reasons. Most people have no comments next to the qualification or insurance but some people have some extra notes. You can see that there is one person who has 4 notes on their drivers license. Their names are in column A and there is a row with nothing in it between people. I want to be able to search for a person and return the row that contains their name and all the rows under it that are relating to that person. Could you help me with that Dante, please?

What would be nice is if I had a text box that I could type in and it would filter the names as I type, be able to stop and enter information once I have found what I am looking for and then delete everything in the text box and for it to return to the spreadsheet before it was filtered.

 

[DanteAmor]

Maybe you have some screen flashes, that's because you can't do what you ask with an autofilter, what I'm doing is hiding and showing the rows.

The code looks for any letter or letters within the name.
But if you only want it to search at the beginning of the name, change this line:

If Not sName Like "*" & TextBox1.Value & "*" Then

By this line:

If Not sName Like TextBox1.Value & "*" Then

Put the following code in your textbox code.

Private Sub TextBox1_Change()
  Dim lr1 As Long, lr2 As Long
  Dim ar As Range, rng As Range
  Dim sName As String
  
  Application.DisplayAlerts = False
  
  If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
  ActiveSheet.Cells.EntireRow.Hidden = False
  
  lr1 = Range("A:D").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row + 1
  Range("A" & lr1 + 1).Value = "xlend"
  Set rng = Range("A" & lr1 + 1)
  
  For Each ar In Range("A9:A" & lr1).SpecialCells(xlCellTypeBlanks).Areas
    sName = ar.Cells(1).Offset(-1, 0).Value
    If Not sName Like "*" & TextBox1.Value & "*" Then
      Set rng = Union(rng, ar.Offset(-1, 0))
    End If
  Next

  rng.EntireRow.Hidden = True
  Range("A" & lr1 + 1).Clear
  Application.DisplayAlerts = True
End Sub

 

[dpaton05]

Thanks Dante,

Where do I put this line of code?

If Not sName Like "*" & TextBox1.Value & "*" Then

 

[MikeVol]

If Not sName Like "*" & TextBox1.Value & "*" Then Set rng = Union(rng, ar.Offset(-1, 0)) End If

This line is already in the code.

 

[DanteAmor]

Where do I put this line of code?

Don't change anything in the macro.

Just try the macro and tell me the results.

Private Sub TextBox1_Change()
  Dim lr1 As Long, lr2 As Long
  Dim ar As Range, rng As Range
  Dim sName As String
  
  Application.DisplayAlerts = False
  
  If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False
  ActiveSheet.Cells.EntireRow.Hidden = False
  
  lr1 = Range("A:D").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row + 1
  Range("A" & lr1 + 1).Value = "xlend"
  Set rng = Range("A" & lr1 + 1)
  
  For Each ar In Range("A9:A" & lr1).SpecialCells(xlCellTypeBlanks).Areas
    sName = ar.Cells(1).Offset(-1, 0).Value
    If Not sName Like "*" & TextBox1.Value & "*" Then
      Set rng = Union(rng, ar.Offset(-1, 0))
    End If
  Next

  rng.EntireRow.Hidden = True
  Range("A" & lr1 + 1).Clear
  Application.DisplayAlerts = True
End Sub

 

[dpaton05]

Thanks Dante, I put the above code in the change event for the text box and as I type in the text box, some of the data in rows starts to disappear but it doesn't appear to include all rows that have columns that satisfy the criteria. I am trying to type some part of someones name that is in the names column but their name is not included in the search results.

I also want to hide the rows that do not satisfy the criteria so it makes it easier to read.

 

[dpaton05]

....or, in other words, hide the rows that are blank.

 

[dpaton05]

.....actually, I have just thought of a better approach. I think that all I really need is to do the standard search from hitting ctrl+F, only that I want it to use the text in textbox1.