Populate Combobox with Named Range

[samohtwerdna]

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

[Bob Phillips]

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]

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]

Thanks !!

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

[Zack Barresse]

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

[sheeeng]

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]

Great point sheeeng!!