Code works to :
List all unique values from column A in Column B, starting with row
12.

I need to know how to change the criteria in the code to do the
following:

List all unique values from column L, in column Q, starting with row
12.

(I tried changing the obvious letters.... but must be a little more to
it!).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRow As Long
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
If Application.CountIf(Range("B:B"), Target.Value) = 0 Then
rw = Range("B65536").End(xlUp)(2).Row
If rw < 12 Then rw = 12
Cells(rw, 2).Value = Target.Value
End If
For myRow = Range("B65536").End(xlUp).Row To 12 Step -1
If IsError(Application.Match(Cells(myRow, 2).Value, Range("A:A"),
False)) Then
Cells(myRow, 2).ClearContents
End If
Next myRow
Application.EnableEvents = True
End Sub

Why not just use
data>filter>advanced filter>unique values>>>>

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"J.W. Aldridge" <(E-Mail Removed)> wrote in message
news:153a885c-d182-4cc2-b046-(E-Mail Removed)...
> Code works to :
> List all unique values from column A in Column B, starting with row
> 12.
>
> I need to know how to change the criteria in the code to do the
> following:
>
> List all unique values from column L, in column Q, starting with row
> 12.
>
> (I tried changing the obvious letters.... but must be a little more to
> it!).
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Dim myRow As Long
> If Target.Cells.Count > 1 Then Exit Sub
> If Target.Column <> 1 Then Exit Sub
> Application.EnableEvents = False
> If Application.CountIf(Range("B:B"), Target.Value) = 0 Then
> rw = Range("B65536").End(xlUp)(2).Row
> If rw < 12 Then rw = 12
> Cells(rw, 2).Value = Target.Value
> End If
> For myRow = Range("B65536").End(xlUp).Row To 12 Step -1
> If IsError(Application.Match(Cells(myRow, 2).Value, Range("A:A"),
> False)) Then
> Cells(myRow, 2).ClearContents
> End If
> Next myRow
> Application.EnableEvents = True
> End Sub
>
>
>

Need to automate this process which affects another part of
complicated worksheet....

Wont be able to stop and run filter etc.

Code works fine, just need to know what parts are to be altered in
order to change criteria to suite.

Thanx

Record a macro while doing and clean up or post your code afterwards for
comments

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"J.W. Aldridge" <(E-Mail Removed)> wrote in message
news:48f401a9-a6b4-4eb6-a004-(E-Mail Removed)...
> Need to automate this process which affects another part of
> complicated worksheet....
>
> Wont be able to stop and run filter etc.
>
> Code works fine, just need to know what parts are to be altered in
> order to change criteria to suite.
>
> Thanx

Ok....


Tried that.

Macro does...(copy the list, create another page, paste it there,
filter it, bring it back to the original page to the desired cell,
then delete the temp page.)

Post your code for comments and/or send your workbook to my address below

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"J.W. Aldridge" <(E-Mail Removed)> wrote in message
news:069579ec-699b-448b-8e47-(E-Mail Removed)...
> Ok....
>
>
> Tried that.
>
> Macro does...(copy the list, create another page, paste it there,
> filter it, bring it back to the original page to the desired cell,
> then delete the temp page.)
>