Solved: Selecting Cells

[chocho]

Hi,

I'm looking to populate a listbox. In another form, I have set a range of a full row, and this is set as APublicVariable. I am looking to find the value of only the cells with content in that range. Example (All in row 1): cell 1= blank, cell 2=data, cell 3=data, cell 4= blank. I do not know where the data begins or ends, only that it is in row 1. This is what I started at but its not working:

Listbox.List = ThisWorkbook.APublicVariable.UsedRange.Value

As well, when I use this to populate the list on userform initialization, I get an error in the previous userform that directs to this userform. Thanks for your help!

[p45cal]

Take a look at this post's (http://www.vbaexpress.com/forum/showpost.php?p=283881&postcount=8) code (part of this thread (http://vbaexpress.com/forum/showthread.php?t=44951))and adapt;
For each cl in APublicVariable.SpecialCells(2)
and later change the name of the listbox to yours:
lbxExpertise to listbox

Depending on whether the data in your cells are all constants or are all a result of formulae, or a mixture of both, you may have to change the SpecialCells line. Check out the Help on the SpecialCells method. The 2 in there at the moment refers to constants, whether logical, errors, numbers or text.

[chocho]

Worked perfectly, thanks! Kept getting an error until I removed .sort but after that all good.