UDF Sort Function

[luvnrocs]

Hi -

I need a UDF that can sort a column of text and weed out duplicates.
If the weeding of duplicates is not possible I'll just take the sort
function. I can't use Excel's sort function because I need the sort to
happen dynamically when the user enters text in column A. Some cells
in column A could be blank.

Col A contains text strings like
GGG
BBB
<blank>
CCC
AAA
FFF
<blank>
BBB
GGG
<blank>
HHH
AAA
LLL

so with the proper UDF Column B would look like this
AAA
BBB
CCC
FFF
GGG
HHH
LLL


Any help would be greatly appreciated!

 

[Bob Phillips]

Not a UDF< event code

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:A" '<== change to suit
Dim LastRow As Long
Dim i As Long

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then

With Target

Me.Columns(1).Sort key1:=Me.Range("A1"), _
order1:=xlAscending, _
header:=False

LastRow = Me.Cells(Me.Rows.Count, .Column).End(xlUp).Row
For i = LastRow To 1 Step -1

If Me.Cells(i, .Column).Value = Me.Cells(i - 1,
..Column).Value Then

Me.Rows(i).Delete
End If
Next i
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)