+ Reply to Thread
Results 1 to 9 of 9

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

  1. #1
    gulfera63
    Guest

    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. #2
    Norman Jones
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #3
    Rob
    Guest

    RE: 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


  4. #4
    Registered User
    Join Date
    09-28-2012
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2010
    Posts
    7

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

    Wow. I registered just to respond to this thread. Thank you Rob for this snippet of code. This is amazingly easy to use and works perfectly.
    Daniel

  5. #5
    Registered User
    Join Date
    09-28-2012
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2010
    Posts
    7

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

    I found a small flaw in this macro after using it for some time. If ESC or CANCEL was pressed the macro would proceed to filter as if ENTER was pressed. I've included a minor update to address the issue.
    Please Login or Register  to view this content.
    Thanks.
    Last edited by arlu1201; 12-20-2012 at 03:31 PM.

  6. #6
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

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

    DBonnell,

    Welcome to the forum.

    I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  7. #7
    Forum Contributor dlow's Avatar
    Join Date
    08-12-2013
    Location
    EveryWhere
    MS-Off Ver
    Excel 2007, 2010
    Posts
    275

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

    Thanks everybody 4 Q&A. Lots of help with the issue I had.

  8. #8
    Registered User
    Join Date
    09-28-2012
    Location
    Virginia, USA
    MS-Off Ver
    Excel 2010
    Posts
    7

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

    Anyone out there that can help with this one... I need to be able to get this code to react properly if I select a cell with an error (#N/A). I know the problem is in the "Operator:=xlOr, " area - you can see where I was toying with an "If" to catch "If the active cell was #N/A" but I can't seem to crack it...

    Please Login or Register  to view this content.

  9. #9
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

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

    DBonnell,

    Please check post 6 of this thread.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1