Populate Combobox with Named Range

Hello all,

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

Code:

---

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

---

I want

Code:

---

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:

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.

You could perform a simple loop if you'd like ...

Code:

---

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 ...

Code:

---

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

---

Thanks !!

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

You are very welcome. I'll go ahead and mark this thread as Solved then. :)

Quote:

---

Originally Posted by samohtwerdna

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

Great point sheeeng!!