+ Reply to Thread
Results 1 to 3 of 3

Filter on cell value?

  1. #1
    Registered User
    Join Date
    06-16-2006
    Location
    Tamworth, Staffordshire, UK
    Posts
    19

    Filter on cell value?

    Is it possible to have a column of multiple values and by clicking in the cell, it filters on that value?

  2. #2
    Chip Pearson
    Guest

    Re: Filter on cell value?

    Try

    Dim WS As Worksheet
    Set WS = ActiveSheet
    WS.UsedRange.AutoFilter
    WS.UsedRange.AutoFilter Field:=ActiveCell.Column,
    Criteria1:=ActiveCell.Value


    --
    Cordially,
    Chip Pearson
    Microsoft MVP - Excel
    Pearson Software Consulting, LLC
    www.cpearson.com



    "QuickLearner"
    <[email protected]> wrote
    in message
    news:[email protected]...
    >
    > Is it possible to have a column of multiple values and by
    > clicking in
    > the cell, it filters on that value?
    >
    >
    > --
    > QuickLearner
    > ------------------------------------------------------------------------
    > QuickLearner's Profile:
    > http://www.excelforum.com/member.php...o&userid=35483
    > View this thread:
    > http://www.excelforum.com/showthread...hreadid=556224
    >




  3. #3
    Mark Driscol
    Guest

    Re: Filter on cell value?

    You could attach the following code to a menu item, and then clicking
    the menu item would filter based on the ActiveCell. (I think the way
    this will post will show some lines as wrapping.)

    Sub FilterOnActiveCell()

    Dim j As Long

    If ActiveSheet.AutoFilterMode = False Then
    ' If the sheet does not have an Autofilter, exit
    MsgBox "There is no filter on this sheet."
    Exit Sub
    Else
    If Intersect(ActiveSheet.AutoFilter.Range, ActiveCell) Is
    Nothing Then
    ' No overlap between AutoFilter range and ActiveCell
    MsgBox "The ActiveCell is not within the filtered range."
    Exit Sub
    Else
    j = ActiveCell.Column -
    ActiveSheet.AutoFilter.Range.Columns(1).Column + 1
    If Application.IsText(ActiveCell) Then
    ' Cell to filter on may contain blanks, so leave them
    in
    ActiveSheet.AutoFilter.Range.AutoFilter _
    Field:=j, _
    Criteria1:=ActiveCell.Text
    Else
    ' Take out any blanks in ActiveCell.Text that may
    result from the way
    ' numbers are formatted, e.g., from accounting format
    ActiveSheet.AutoFilter.Range.AutoFilter _
    Field:=j, _
    Criteria1:=Application.Substitute(ActiveCell.Text,
    " ", "")
    End If
    End If
    End If
    End Sub


    Mark


    QuickLearner wrote:
    > Is it possible to have a column of multiple values and by clicking in
    > the cell, it filters on that value?
    >
    >
    > --
    > QuickLearner
    > ------------------------------------------------------------------------
    > QuickLearner's Profile: http://www.excelforum.com/member.php...o&userid=35483
    > View this thread: http://www.excelforum.com/showthread...hreadid=556224



+ 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