dynamic filter

  • Thread starter Thread starter Gary
  • Start date Start date
G

Gary

Anyone have experience using a dynamic filter within excel?

My thought is to have an excel table with say first name, last name, phone,
and room all in seperate columns and enabling the autofilter feature.

What would be cool is to be able to begin typing the last name (over the
"last name" field) and to have the table dynamically reduce as each
character is typed (or at least have the filter run on the enter key). The
same would happen over the other fields.

Probably alot more complicated than my simple mind can handle but it seems
this could be done with a few VB commands. Any ideas?

Thanks in advance!
Gary
 
Wouldn't it be easier to select a name from the AutoFilter dropdown
list, than to type the name above the table?

But if you want to filter for part of a name, you could use an Advanced
Filter, and write event code to run when the criteria cell is changed.
 
Or you could use a button and an inputbox

Sub Last_Name()
Application.ScreenUpdating = False
UserVal = Application.InputBox("Enter Last Name")
If UserVal = False Then
Exit Sub
Else
Selection.AutoFilter Field:=2, Criteria1:=UserVal & "*", Operator:=xlAnd
End If
Application.ScreenUpdating = True
End Sub

It can be attached to a button, press the button and enter a few letters of
the last name,
press enter, or phone numbers

Sub Area_Code()
Application.ScreenUpdating = False
UserVal = Application.InputBox("Enter Area Code")
If UserVal = False Then
Exit Sub
Else
Selection.AutoFilter Field:=3, Criteria1:=UserVal & "*", Operator:=xlAnd
End If
Application.ScreenUpdating = True
End Sub

Where you can filter on the area code, finally you can have a reset button
that resets all
filters
 
Thanks for your input Debra. Yes it is pretty simple to use the autofilter
drop down list, but what I don't like about that method is that you first
have to de-select "all", then scroll down a potentially huge list of records
assuming you know the correct spelling of the entire name. I may have to go
that route anyway.

Thanks,
Gary
 
Way cool and easier than I thought. Despite my weak VB skills, I am going to
try to see if I can make this work off the entry of text in a cell. Perhaps
when I clear the cell, the filter is reset for that field.

Thanks!
Gary
 
Peo! Good news but still need help.

I was able to link the script to the contents of a cell but I need a little
help on cleanup. I have a button attached to the modified macro:
Sub First_Name()
Application.ScreenUpdating = False
UserVal = Application.Range("B3")
If UserVal = "" Then
Selection.AutoFilter Field:=2
Else
Selection.AutoFilter Field:=2, Criteria1:=UserVal & "*", Operator:=xlAnd
End If
Application.ScreenUpdating = True
End Sub
Click to expand...

I would like to get this macro to run when the contents in cell "B3" changes
(on enter) and the selection stays on "B3". Any suggestions on how to
accomplish this in a clean fashion? I am not sure where to start on this
one.

Thanks,
Gary
 
Here is a macro that reset all autofilters

Sub Reset_Filter()
Application.ScreenUpdating = False
For Each sh In Worksheets
If sh.FilterMode Then
On Error Resume Next
sh.ShowAllData
End If
Next
Range("A1").Select
Application.ScreenUpdating = True
End Sub

You can remove

Range("A1").Select

if you want, it selects cell A1 after the reset, that
was what the users of the sheet wanted..
If you want to trigger any macros upon changing B3 do a search (google)
for

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

and you'll get many hits.
 
Thanks for the help everyone! Through your help and some searching on the
internet I came up with a pretty cool script that does 99% of what I wanted.
Check it out. Since I need this to work for all fields I'll need to tweek a
bit more. Here is the code (for field 2):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 2 And Target.Row = 3 Then
ThisRow = Target.Row
Application.ScreenUpdating = False
UserVal = Application.Range("B3")
If UserVal = "" Then
Selection.AutoFilter Field:=2
Else
Selection.AutoFilter Field:=2, Criteria1:=UserVal & "*", Operator:=xlAnd
End If
Application.ScreenUpdating = True
If Target.Value > 100 Then
Range("B" & ThisRow).Interior.ColorIndex = 19
Else
Range("B" & ThisRow).Interior.ColorIndex = 15
End If
End If
End Sub

Thanks again,
Gary
 
Another question: Now that I have a working macro for the dynamic
filterering, I realize I need to duplicate this for each autofilter field.
What would be the best way to approach this? Should I create several "If
Then" statements which look to see which cell is selected then run a
particular macro for the field below that cell (picture an autofilter table
with a cell above each field for entering filter strings)? Or do I create
some sort of loop? Would everything run under "Private Sub
Worksheet_Change(ByVal Target As Excel.Range)"? The working macro is below.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 2 And Target.Row = 3 Then
ThisRow = Target.Row
Application.ScreenUpdating = False
UserVal = Application.Range("B3")
If UserVal = "" Then
Selection.AutoFilter Field:=2
Else
Selection.AutoFilter Field:=2, Criteria1:=UserVal & "*", Operator:=xlAnd
End If
Application.ScreenUpdating = True
If Target.Value > 100 Then
Range("B" & ThisRow).Interior.ColorIndex = 19
Else
Range("B" & ThisRow).Interior.ColorIndex = 15
End If
End If
End Sub

I don't understand some of this macro stuff above, but I guess I don't
really need to. Any direction is greatly appreciated.

Thanks,
Gary
 
Back
Top