PDA

View Full Version : [SOLVED] Populate Combobox with Named Range



samohtwerdna
09-13-2005, 09:15 AM
Hello all,

This is im sure a simple question, but I want to populate a comboBox with a named range so rather than:

With cboLookUp
.List = Range("K2:K236").Value
End With
I want

With cboLookUp
.List = Range("JOBNAME").Value
End With
my problem is that I want "JOBNAME" to only refer to the cells that actually have a value in them so I don't get a populated comboBox with a bunch of blank values.

Any help??:doh:

Bob Phillips
09-13-2005, 09:36 AM
You would either have to loop through the range and pick out the non-blanks, or copy the non-blanks to a new range and link to that.

Zack Barresse
09-13-2005, 09:53 AM
You could perform a simple loop if you'd like ...


Dim c As Range, r As Range
Set r = Range("JOBNAME")
With cboLookUp
For each c in r
.AddItem = c.Value
Next c
End With

.. or with a check for blank cells ...


Dim c As Range, r As Range
Set r = Range("JOBNAME")
With cboLookUp
For each c in r
if c <> vbNullString Then .AddItem = c.Value
Next c
End With

samohtwerdna
09-13-2005, 10:31 AM
Thanks !!

I knew it was a simple answer. "vbNullString" of course.

Zack Barresse
09-13-2005, 10:57 AM
You are very welcome. I'll go ahead and mark this thread as Solved then. :)

sheeeng
09-14-2005, 07:34 AM
Thanks !!

I knew it was a simple answer. "vbNullString" of course.

Well, Actually "" & vbNullString makes not much difference...

Refer to Optimize String Handling in Visual Basic

http://vbaexpress.com/forum/showthread.php?p=43498

Zack Barresse
09-14-2005, 08:35 AM
Great point sheeeng!!