Excel; Can I Filter based on a cell value?

S

setoFairfax

Is it possible to filter a list based on a cell value? What I want to do is for the user to type in a criteria in cell F1 and G1 (Dept and Acct No's) and click a button that will launch a macro that will filter the list and display only entries that have Dept and Acct No's that match those entered in F1 and G1.

I have looked through the help files in Excel and can't find a way to do this.

If this is not possible is it possible to set something up so that cells that do not fit the criteria are deleted from the spreadsheet somehow? It is ok if non-matching entries are deleted since this list is created when it opens from a protected master-list.
 
B

Bob Phillips

Here is some code. It assumes that you want to filter columns A and B and
that the filter is already set on those columns

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Address = "$F$1" Then
SetAutofilter Columns("A:A"), .Value, 1
ElseIf .Address = "$G$1" Then
SetAutofilter Columns("B:B"), .Value, 2
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

'-----------------------------------------------------------------
Public Sub SetAutofilter(col, val, fld)
'-----------------------------------------------------------------
With col
If val <> "" Then
.AutoFilter Field:=fld, Criteria1:=val
Else
.AutoFilter Field:=fld
End If
End With
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

setoFairfax said:
Is it possible to filter a list based on a cell value? What I want to
Click to expand...
do is for the user to type in a criteria in cell F1 and G1 (Dept and Acct
No's) and click a button that will launch a macro that will filter the list
and display only entries that have Dept and Acct No's that match those
entered in F1 and G1.
I have looked through the help files in Excel and can't find a way to do this.

If this is not possible is it possible to set something up so that
Click to expand...
cells that do not fit the criteria are deleted from the spreadsheet somehow?
It is ok if non-matching entries are deleted since this list is created when
it opens from a protected master-list.
 
Top