Change criteria for worksheet change code.
 

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

Change criteria for worksheet change code.
 

Why not just use
datafilteradvanced filterunique values

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"J.W. Aldridge" wrote in message
...
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

Change criteria for worksheet change code.
 

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

Change criteria for worksheet change code.
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"J.W. Aldridge" wrote in message
...
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

Change criteria for worksheet change code.
 

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

Change criteria for worksheet change code.
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"J.W. Aldridge" wrote in message
...
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.)