Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If activecell.column = variable then activecell,offset (0,1) | Excel Discussion (Misc queries) | |||
Displaying AutoFilter criteria | Excel Programming | |||
AutoFilter Criteria VBA | Excel Programming | |||
Setting autofilter criteria | Excel Programming | |||
VBA Autofilter Criteria | Excel Programming |