Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Automatically Autofilter With Cell Value On Selection

  1. #1
    Join Date
    23rd February 2008
    Posts
    6

    Automatically Autofilter With Cell Value On Selection

    I would like to use the autofilter function in an excel spreadsheet, with the filter value being a cell reference.

    Per the following thread http://www.ozgrid.com/forum/showthread.php?t=23186 I created the routine in the attached spreadsheet. I have no experience with visual basic. The routine works when I execute "run/run sub/user form" from the Microsoft visual basic toolbar. However, I don’t know how to execute the routine automatically when a user enters a value in the cell in question.

    Alternatively, is there an easier way to accomplish the same using a formula or function?

    Thanks
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Last edited by Dave Hawley; February 23rd, 2008 at 07:57.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Autofilter With Cell Value

    You need to put your code in the worksheet module, Selection Change event.

    Copy and paste the code below to the worksheet module, which you can quickly access by right-clicking on the sheet tab and selecting View Code from the context menu.

    VB:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
         
        ActiveSheet.AutoFilterMode = False 
        Range("A3:D3").AutoFilter 
        Range("A3:D3").AutoFilter Field:=2, Criteria1:=Range("A2").Text 
         
         
    End Sub 
    
    
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  3. #3
    Join Date
    23rd February 2008
    Posts
    6

    Re: Autofilter With Cell Value

    Thanks! THis was extremely helpful! Daniel

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    28th July 2010
    Posts
    3

    Re: Autofilter With Cell Value

    AAE,

    What would I have to change in the above code to add to the autofilter a greater than and less than filter based on a value in another cell?

    Thanks.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    28th July 2010
    Posts
    2

    Re: Automatically Autofilter With Cell Value On Selection

    eyanosa,

    You would need to add just one more line for each additional filter

    VB:
    Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
         
        ActiveSheet.AutoFilterMode = False 
        Range("A3:D3").AutoFilter 
        Range("A3:D3").AutoFilter field:=2, Criteria1:=Range("A2").Text 
        Range("A3:D3").AutoFilter field:=3, Criteria1:=">" & Range("B2").Value 'greater than filter to Contract ("B2" as criteria)
        Range("A3:D3").AutoFilter field:=4, Criteria1:="<" & Range("C2").Value 'less than filter to Op Code ("C2" as criteria)
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. AutoFilter Automatically Based On Cell Value
    By zeirz in forum EXCEL HELP
    Replies: 6
    Last Post: November 18th, 2012, 23:51
  2. Replies: 2
    Last Post: July 31st, 2008, 22:39
  3. Automatically Refresh Autofilter
    By siweltj in forum EXCEL HELP
    Replies: 3
    Last Post: May 1st, 2007, 18:58
  4. Autofilter Then Cell Selection
    By Mills in forum EXCEL HELP
    Replies: 1
    Last Post: April 5th, 2007, 13:49
  5. Selection.Autofilter numbers vs letters
    By gmt_v_oz in forum EXCEL HELP
    Replies: 6
    Last Post: January 22nd, 2005, 00:00

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
  •  
porno