Change criteria for worksheet change code.

[J.W. Aldridge]

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

 

[Don Guillett]

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

 

[J.W. Aldridge]

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

 

[Don Guillett]

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

 

[J.W. Aldridge]

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.)

 

[Don Guillett]

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