unique list

[Nigel]

the code i have so far is...

Private Sub NewList()
Dim c As Range
Dim LastC As Range
Dim i As Integer
Set LastC = Range("A65354").End(xlUp)
For Each c In Range("A2", LastC)
If c <> "" Then
Range("B2").Offset(i, 0) = c.Value
i = i + 1
End If
Next c
End Sub

this takes the blanks out of a range, but i need to also incorporate
only copying unique cells......

so converting ColA to ColB

ColA ColB

test1 test1
test2 test2
test1 test3
<blank>
test3
<blank>
test1

and then how making a named range out of ColB would be really helpful
too...or at least finding the last cell (test3 in the example)

 

[Tom Ogilvy]

Private Sub NewList()
Dim c As Range
Dim LastC As Range
Dim i As Integer
Set LastC = Cells(rows.count,1).End(xlUp)
For Each c In Range("A2", LastC)
If len(trim(c)) > 0 Then
if application.Countif(Columns(2),c) = 0 then
Range("B2").Offset(i, 0) = c.Value
i = i + 1
End if
End If
Next c
if Not isempty(Range("B3")) then
Range("B2",Range("B2").end(xldown)).Name = "List1"
Else
Range("B2").Name = "List1")
End if
End Sub