UDF

Hi all,

I'm trying to create a user-defined function (UDF) that creates a sublist of all items in an 'original list' such that the sublist will return only unique values from the original list (ie. items that appear more than once in the original list will only appear once in the subset list). The function needs to cater for a dynamic origanl list such that if new items are added to the original list the subset list will automatically be updated (provided that it is not already listed in the sublist).

I am having trouble creating a UDF that will return such an array of values. The 'original list' range is automatically updated because it is imported data and thus receives a Name (i.e. ExtrenalData1) that automatically encompasses all of the data. The function I have written so far looks like this:

Function ListContents(NamedRange As String) As Variant

Dim CellsToCount As Object
Dim RowToCheck As Object
Dim Cell As Object
Dim i As Integer
Dim Max As Integer
Dim Address As String
Dim InList As Boolean

Dim ListContent () as string

Application.Volatile
Max = 0
InList = False

Set CellsToCount = Range(NamedRange) 'sets the list range
For Each Cell In CellsToCount
Address = Cell.Address 'gets the cell address for the cells in the For Each ... Loop
Set RowToCheck = Range(Address).CurrentRegion.Rows 'gets the row for the cells in the loop and then checks to see if it is hidden
If RowToCheck.Hidden = False And Cell.Value <> "" Then 'if the row is hidden then End If otherwise next For ...