MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by David on June 14, 2000 12:14 AM

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

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 ...

Posted by Ryan on June 14, 0100 2:44 AM

Just at a quick glance, I can tell you that Functions do not perform operations. A function is used to return a value. Instead of declaring the procedure as a Funtion, just declare it as a normal procedure and see what happens.
Hope this helps,