ListBox population problem...

[Matty]

Hi,

I'm using the following code to populate a ListBox:

    LR = Sheet2.Range("D" & Rows.Count).End(xlUp).Row
    
    With lbxTest
        .Clear
        For i = 1 To LR
            .AddItem Sheet2.Range("D" & i).Value
        Next i
    End With

On occasions, column D of Sheet2 will be empty. When this happens, I want my ListBox to be empty, i.e. no tick boxes.

At the moment a single tick box is added when column D is empty, so how can I prevent this from happening?

Cheers,

Matty

 

[Fennek]

Hello,

have a look to

http://snb-vba.eu/VBA_Fill_combobox_listbox_en.html

particular to chapter 4.

regards

 

[Matty]

Hi,

Thanks for the link. It looks a useful resource, but I can't see anything to handle my particular issue.

Anyone any other ideas?

Cheers,

Matty

 

[RoryA]

I'd suggest just checking D1:

    LR = Sheet2.Range("D" & Rows.Count).End(xlUp).Row
    lbxTest.Clear
    If LR > 1 then
    lbxTest.List Sheet2.Range("D1:D" & LR).Value
     Else
        if len(Sheet2.Range("D1").Value <> 0 then lbxTest.AddItem Sheet2.Range("D1").Value
    End If