Excel VBA - ignoring blanks and sorting data in lists

[royend]

Hi,

I have made a drop-down list, and it works in its way. It gets dat
from C6:C500, which contains data as it is filled out. At m
beta-version it contains data from A6:A8.

This means that my list includes a lot of blanks at the moment.
would like to have my list ignore them, and just show the cells tha
contains data. Is this possible?

In addition, it would be better if the list where sorte
alphabetically. The list contains names, and an alphabetical orde
would make it easier to choose the correct name between hundreds o
names.

Thanks in advance for every hint and tip.

Best Regards.
Roy Endr

 

[Tom Ogilvy]

Dim cell as Range, varr as Variant
for each cell in Range("C6:C500").SpecialCells(xlConstants,xlTextValues)
if cell.Text <> "" then
Combobox1.Additem cell.Value
end if
Next

If you want to sort them, then can adapt John Walkenbach's sample code:

http://j-walk.com/ss/excel/tips/tip47.htm
Filling a listbox with unique items

Just add code to screen out blanks.