Change criteria for worksheet change code.

  • Thread starter Thread starter J.W. Aldridge
  • Start date Start date
J

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