Once I got this macro edited (copy and paste put a few <enter and odd things
that excel didn't like) so it would run, I got "Unable to get CountIf proerty
of the worksheet function class" error on the line...

If Application.WorksheetFunction.CountIf(ResultRng, Cel.Value) = 0 Then

any guess what I did wrong? I wish I was better with macros. My work with
Access has helped but I am still very weak when it comes to macros.

"JBeaucaire" wrote:


This macro will do it. It has two SET lines, I activated the one that
lets you select a range first. You can deactivate that line and activate
the line above which will cause the macro to ask for the search range,
too.

=============
Sub ListUniqueValues()

'lists the unique values found in a user-defined range into a
'user-defined columnar range

Dim SearchRng As Range
Dim ResultRng As Range
Dim Cel As Range
Dim iRow As Long

'Set SearchRng = Application.InputBox("Select search range", _
' "Find Unique Values", Type:=8)

Set SearchRng = Selection
Do
Set ResultRng = Application.InputBox("Select results columnar
range", _
"Write Unique Values", Type:=8)
Loop Until ResultRng.Columns.Count = 1

iRow = 0
For Each Cel In SearchRng
If Application.WorksheetFunction.CountIf(ResultRng, Cel.Value) = 0
Then
'This value doesn't already exist
iRow = iRow + 1
If iRow ResultRng.Rows.Count Then
MsgBox "Not enough rows in result range to write all unique
values", _
vbwarning, "Run terminated"
Exit Sub
Else
ResultRng(iRow).Value = Cel.Value
End If
End If
Next Cel

'sort result range
ResultRng.Sort ResultRng

End Sub
============

Is this something you can work with?


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48582