Many thanks to you both.

I was trying to use a Collection because I thought I had
read somewhere about it automatically excluding any
duplicates.........sure enough that link to J. W.'s site
confirms this (hence the use of OERN to suppress any
messages).

I imported Tom's code:

However, when I ran the code on my test sheets, it did
not exclude duplicates. Is it worth pursuing (out of
interest) the Collection idea, or perhaps follow the array
route?

Regards.

"Tom Ogilvy" wrote in message
...
I believe it does work (at least it did for me), but you can't see the
result with

msgbox EnquiryList

Sub SortContractorsSuppliers()

Dim ws As Worksheet, LastRow As Long
Dim DataRange As Range, Cell As Range
Dim EnquiryList As New Collection

For Each ws In ActiveWorkbook.Worksheets
With ws
.Unprotect
LastRow = Application.Max(.Range("H65536") _
.End(xlUp).Row, .Range("I65536").End(xlUp).Row, _
.Range("J65536").End(xlUp).Row)
Set DataRange = Range("H2", "J" & LastRow)
On Error Resume Next
For Each Cell In DataRange
If Not IsEmpty(Cell) Then
EnquiryList.Add Cell.Value
End If
Next Cell
On Error GoTo 0
End With
Next
sStr = ""
For Each itm In EnquiryList
sStr = sStr & itm & vbNewLine
Next
msgbox sStr
End Sub


See John Walkenbach's site for an example to follow including sorting

http://j-walk.com/ss/excel/tips/tip47.htm

--
Regards,
Tom Ogilvy








Stuart wrote in message
...
I'm looking to take unique values from a defined range
into a form. The values will be alphabetic characters.
Here is what I have so far:

Sub SortContractorsSuppliers()

Dim ws As Worksheet, LastRow As Long
Dim DataRange As Range, Cell As Range
Dim EnquiryList As New Collection

For Each ws In ActiveWorkbook.Worksheets
With ws
.Unprotect
LastRow = Application.Max(.Range("H65536") _
.End(xlUp).Row, .Range("I65536").End(xlUp).Row, _
.Range("J65536").End(xlUp).Row)
Set DataRange = Range("H2", "J" & LastRow)
On Error Resume Next
For Each Cell In DataRange
If Not IsEmpty(Cell) Then
EnquiryList.Add Cell.Value
End If
Next Cell
On Error GoTo 0
End With
Next

MsgBox EnquiryList

End Sub

Why is it that when the code loops into the 2nd sheet, any
unique values in that sheet's DataRange are not added to the
Collection, please?

Regards.





---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.512 / Virus Database: 309 - Release Date: 19/08/2003