Filter on cell value?
 

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

--
QuickLearne
-----------------------------------------------------------------------
QuickLearner's Profile: http://www.excelforum.com/member.php...fo&userid=3548
View this thread: http://www.excelforum.com/showthread.php?threadid=55622

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"
wrote
in message
news:QuickLearner.2a2vkk_1151440557.4363@excelforu m-nospam.com...

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

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