Further to the recent posts, I have been working on a method to create
Data Validation using sorted, non-duplicated items. This is based on a
John Walkenbach method, ref "Power Programming 2002 VBA", page 423.
Based on a tip by J.G. Hussey, published in "Visual Basic Programmer's
Journal".

Data Validation requires that the List Source must be a delimited list
or a reference to a single row or column.

My methods are to create Data Validation in VBA by using either :

1. a delimited list (VBA string variable, effectively held in memory),
or
2. a worksheet range.

The former is limited to 256 characters, OK for most "normal"
purposes. The latter requires writing to the worksheet as I have been
unable to implement a range "in memory" that works with Data
Validation. I've tried all sorts of naming techniques, no luck. If
anyone has an ideas, it would finish off this approach to removing
duplicates from Data Validation.

If anyone is interested in examples, please ask.

Regards,

Mark

Mark,

If you just want to create a named list of valid entries that can change
easily, use a dynamic named range.

For example, if your list starts in cell B3 on Sheet1, and nothing other
than your list is in column B, then use

Insert | Name | Define... and enter a name, like

DataList

and in the 'Refers to:' box, enter

=OFFSET(Sheet1!$B$3,0,0,COUNTA(Sheet1!$B:$B),1)

Then in your data validation list, use

=DataList

HTH,
Bernie
MS Excel MVP
"Mark Worthington" wrote in message
om...
Further to the recent posts, I have been working on a method to create
Data Validation using sorted, non-duplicated items. This is based on a
John Walkenbach method, ref "Power Programming 2002 VBA", page 423.
Based on a tip by J.G. Hussey, published in "Visual Basic Programmer's
Journal".

Data Validation requires that the List Source must be a delimited list
or a reference to a single row or column.

My methods are to create Data Validation in VBA by using either :

1. a delimited list (VBA string variable, effectively held in memory),
or
2. a worksheet range.

The former is limited to 256 characters, OK for most "normal"
purposes. The latter requires writing to the worksheet as I have been
unable to implement a range "in memory" that works with Data
Validation. I've tried all sorts of naming techniques, no luck. If
anyone has an ideas, it would finish off this approach to removing
duplicates from Data Validation.

If anyone is interested in examples, please ask.

Regards,

Mark

Bernie,

Thanks for that. Yes, I am familiar wirth dynamic named ranges, but the
issue here is to remove duplicates from a list such that only unique
items appear in the Daat Validation drop down (or a UserForm etc etc
etc).

What's more, it's trying to do without using the worksheet other than to
get the original list (with duplicates) that is the last problem ....

Regards,

Mark


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Mark,

You're going to have to use a helper range. Visit Chip's website,
specifically

http://www.cpearson.com/excel/duplicat.htm

and read the section

Extracting Unique Entries

and also read "Eliminating Blank Cells" at

http://www.cpearson.com/excel/noblanks.htm

It's all fairly complicated.

Or you could use the worksheet's change event to add any newly entered
unique values to an already established list of unique values, along the
lines of:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Application.CountIf( _
Target.EntireColumn, Target.Value) = 1 Then
Range("C65536").End(xlUp)(2).Value = Target.Value
End If
End If
End Sub

which will store unique entries from column A in column C. There should be
some error checking in there, but you get the idea.

HTH,
Bernie
MS Excel MVP


"Mark Worthington" wrote in message
...
Bernie,

Thanks for that. Yes, I am familiar wirth dynamic named ranges, but the
issue here is to remove duplicates from a list such that only unique
items appear in the Daat Validation drop down (or a UserForm etc etc
etc).

What's more, it's trying to do without using the worksheet other than to
get the original list (with duplicates) that is the last problem ....

Regards,

Mark


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Bernie,

Thanks for the great links! Chip certainly puts some good stuff on his
site.

However, while I appreciate that much of this is done with worksheet
functions (often the better way), I am keen on a VBA solution. The John
Walkenbach method, ref "Power Programming 2002 VBA", page 423, based on
a tip by J.G. Hussey, published in "Visual Basic Programmer's Journal"
is very neat indeed : it uses a Collection object, and takes advantage
of the fact that the key argument must be a unique text string ….

Set My_List = Range("My_List") ' Note, source is a Dynamic
Named Range

' The next statement ignores the error caused by attempting to add
a duplicate
' key to the Collection object. The duplicate is not added - which
is just
' what we want! Note: the 2nd argument (key) for the Add method
must be a string

On Error Resume Next
For Each Cell In My_List
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell

Done. The items in NoDupes can populate a UserForm, a VBA variable, a
worksheet range … you've got to admit, that's pretty cool!

What's more, the list can be sorted :

' Sort the collection (optional). This procedure works well for
normal lists, but
' can be slow with very large lists

For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, befo=j
NoDupes.Add Swap2, befo=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

I like your Worksheet Change idea. One question : the code End(xlUp)(2)
is the same as End(xlUp).Cells(2, 1).Value (I worked out, eventually!).
Is the (2) some shortcut notation?

Cheers,

Mark,



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Mark,

I like your Worksheet Change idea. One question : the code End(xlUp)(2)
is the same as End(xlUp).Cells(2, 1).Value (I worked out, eventually!).
Is the (2) some shortcut notation?

Yes, it is shorcut notation. You don't need the .Cells, or the default when
it is 1.

Range("C65536").End(xlUp).Cells(2, 1).Value
Range("C65536").End(xlUp)(2, 1).Value
Range("C65536").End(xlUp)(2).Value

are all the same.

Likewise, these

Range("C65536").End(xlUp).Cells(1,2).Value
Range("C65536").End(xlUp)(1,2).Value
Range("C65536").End(xlUp)(,2).Value

are all the same, too.

HTH,
Bernie
MS Excel MVP

Bernie,

I also discovered, today that Cells(1, 2) is equivalent to Cells(1,
"B") .... interesting.

Mark