ExcelBanter - Is a Collection the best option?

I could be wrong, but I think that collections are groups of variables or
objects, not values.
(I have made collections of form controls to simulate control arrays in VB6
in Excel VBA.)

What you may need is a dynamic array. Declare it with empty parentheses and
redim it when you add an item. Use the keyword Preserve to avoid erasing
the data in the array. That works well for 1 dimensional arrays, but if you
need a two or more column array, it gets kinda hairy.

In your code, substitute Dim EnquiryList() as Variant in the declarations.

Then sub

ReDim Preserve EnquiryList(1 to Ubound(EnquiryList)+1) 'expand array
size by one
EnquiryList(Ubound(EnquiryList)) = Cell.Value 'put
unique value in array

for EnquiryList.Add Cell.Value

If you just want to add these unique values to a list box in a form, use the
additem method and don't bother with the array.

ListBox1.AddItem Cell.Value

Hope this helps.

"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