Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Automatically Autofilter With Cell Value On Selection

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    Last edited by Dave Hawley; February 23rd, 2008, 08:57.

  • #2
    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.

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

    Comment


    • #3
      Re: Autofilter With Cell Value

      Thanks! THis was extremely helpful! Daniel

      Comment


      • #4
        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.

        Comment


        • #5
          Re: Automatically Autofilter With Cell Value On Selection

          eyanosa,

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

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

          Comment

          Trending

          Collapse

          There are no results that meet this criteria.

          Working...
          X