Extracting unique values from live list
JW,
Use this:
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
Range("B65536").End(xlUp)(2).Value = Target.Value
End If
For myRow = Range("B65536").End(xlUp).Row To 2 Step -1
If IsError(Application.Match(Cells(myRow, 2).Value, Range("A:A"), False)) Then
Cells(myRow, 2).Delete
End If
Next myRow
Application.EnableEvents = True
HTH,
Bernie
MS Excel MVP
"J.W. Aldridge" wrote in message
ups.com...
DIsregaurd that.... I got the first code to work fine. The only thing
is, if they make a typo, the extracted value remains in column B. I
guess I need it to loop if possible and somehow continue to look at row
A to find the unique values.
Thanx
|