Filter on cell value?

[QuickLearner]

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

[Chip Pearson]

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
>

[Mark Driscol]

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