I would think you'd want to use the worksheet_change event. (You can change a
cell without changing the selection.)

Remember that Advanced Filter expects headers in the List range (m18 is the
header??) and will put that same header in the "copy to" range.

Steve E wrote:

Hi,

In XL2003.
I have a range of cells (M18:M32) that can have as many as 15 different
entries and on the same worksheet I am trying to list the unique entries
(normally 3 - 4 are actually unique within that range). I want the list of
unique entries to update itself whenever an entry is made into the range.
I'm trying a Worksheet_SelectionChange event as follows using some code that
I found by Ron Coderre in the group posted about a year ago...:

Option Explicit
Sub Worksheet_SelectionChange(ByVal Target As Range) 'ExtractUnique()
With Target
If .Count 1 Then Exit Sub
If Not Intersect(Me.Range("M18:M32"), .Cells) Is Nothing Then
Application.EnableEvents = False
Me.Unprotect (PWORD_Worksheet)
Me.Range("M18:M32").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("M36"), Unique:=True
Me.Protect (PWORD_Worksheet)
Application.EnableEvents = True
End If
End With
End Sub

I want to use the results in this list as criteria for a VLOOKUP function if
there is an entry in M36...

When I make a change in my range... nothing happens...

Can anyone tell me what I've gotten wrong?

TIA,

SteveE

--

Dave Peterson