How do I set autofilter criteria as the ActiveCell Value in VBA?
 

Trying to create a macro with an autofilter criteria that refers to the
active cell value, so that changing the active cell will change the filtering
criteria.
I looked through help and knowledge base but cicn't find an answer, I'm not
a proficient user though.
Thanks

How do I set autofilter criteria as the ActiveCell Value in VBA?
 

Hi Gulfera63,

Try something like:

Sub Tester()
Dim rng As Range

Set rng = ActiveCell
Range("A1").AutoFilter Field:=1, Criteria1:=rng.Value

End Sub

---
Regards,
Norman



"gulfera63" wrote in message
...
Trying to create a macro with an autofilter criteria that refers to the
active cell value, so that changing the active cell will change the
filtering
criteria.
I looked through help and knowledge base but cicn't find an answer, I'm
not
a proficient user though.
Thanks

How do I set autofilter criteria as the ActiveCell Value in VBA?
 

Hi gulfera63,
Your post is a couple of months old now but just in case you are set up to
receive updates when replied to, you may find this macro handy. I use it on a
daily basis to quickly filter excel databases. I assigned a shortcut key to
it (using [alt+F8] - options) & have it in my personal.xls file. Running the
macro opens an input box for a user response. Leaving the input box blank &
pressing [enter] results in it filtering the current column for the active
cell's value (or any cells which include the string in the active cell);
entering a [space] into the input box removes any filtering from the current
column; or entering a "-" results in the column filtering to exclude what is
in the active cell.

Sub QuickFilter()
' Macro to allow fast filtering of Excel database lists
Application.ScreenUpdating = False
Dim FilterValue As String
Dim CurrentColumn As Long
CurrentColumn = ActiveCell.Column
Dim InputResponse As String
InputResponse = InputBox("Please enter the value to filter this column by.",
"QUICK FILTER")
If InputResponse = " " Then 'removes any filter on current column if a
[space] is input
Selection.AutoFilter Field:=CurrentColumn
Else
If InputResponse = "" Or InputResponse = "-" Then
FilterValue = ActiveCell.Value
Select Case InputResponse
Case Is = ""
Selection.AutoFilter Field:=CurrentColumn,
Criteria1:=FilterValue, Operator:=xlOr, _
Criteria2:="=*" & FilterValue & "*"
Case Is = "-"
Selection.AutoFilter Field:=CurrentColumn, Criteria1:="<" &
FilterValue
End Select
Else
FilterValue = InputResponse
Selection.AutoFilter Field:=CurrentColumn, Criteria1:=FilterValue,
Operator:=xlOr, _
Criteria2:="=*" & FilterValue & "*"
End If
End If
Application.ScreenUpdating = True
End Sub

HTH,
Rob

"gulfera63" wrote:

Trying to create a macro with an autofilter criteria that refers to the
active cell value, so that changing the active cell will change the filtering
criteria.
I looked through help and knowledge base but cicn't find an answer, I'm not
a proficient user though.
Thanks