Combo box - Unique values without blanks.

[Rob W]

Hello,

Im an excel 2007 user and am having problems populating a combo box.
The code below runs a loop for every single row on the worksheets and adds
values from column G (Diagnosis) into the combo box where it is a text
value.

However there are duplicates, i wish the combo box to contain UNIQUE values
only.

Now I dont want to go off on a tangent, but I did think about using custom
views and an advanced filter to select unique values and then ALSO use goto
Special and apply the condition to look for text values ONLY (i.e. removing
the rows with BLANK values).

However the custom view was not able to peform both operations and therefore
could not apply it to the combo box.

Can anyone suggest the best way I can update the combo box removing
duplicates and blank cell values??


Thanks in advance
Rob


Dim lRowEnd As Long
Dim R As Range
Dim wsData As Worksheet

Set wsData = Sheets("Data")
lRowEnd = wsData.Cells(Rows.Count, "A").End(xlUp).Row

With comboDiagnosis
For Each R In wsData.Range("G1:G" &
lRowEnd).SpecialCells(xlCellTypeConstants, 2)
.AddItem CStr(wsData.Cells(R.Row, "G").Value)
Next R
End With

 

[Dave Peterson]

John Walkenbach shows how to loop through a range to get the unique values here:
http://j-walk.com/ss/excel/tips/tip47.htm