dynamic list to remove duplicates and spaces ?

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Gjones (anonymous_at_discussions.microsoft.com)
Date: 06/18/04 

Date: Fri, 18 Jun 2004 06:54:47 -0700

Hi Keith;

Take the column for the sort list and select one of the
cells. The use the code

ActiveCell.EntireColumn.Select

Then filter the list to some where else maybe even a
different *** with the unique values using this code.

Range(Selection.Address).AdvancedFilter
Action:=xlFilterCopy, CopyToRange:=Range("G1" _
        ), Unique:=True

You can change the CopyToRange to be the *** and range
you want

Then set the combo box to read the CopyToRange

Thanks,

Greg

>-----Original Message-----
>Hi,
>
>I have a drop-down box I'd like to fill with unique,
alphabetized
>names sourced from another ***. However, the list of
names on the
>other *** contains duplicate name entries and varies in
length as
>the list is updated.
>
>Currently I reference the entire column that contains the
names, which
>results in duplicate entries, and many many spaces.
>
>Is there a way to dynamically reference this list so that
the
>drop-down doesn't have any duplicates or spaces?
Preferably I'd like
>to do this in the ListFillRange property of the combobox,
and without
>any VB code.
>
>Thanks,
>
>Keith
>.
>

Quantcast