Get values from columns with variable amount of rows to a list box in a userform?

[Rob*]

Hi.

I need som VBA help.
I have a ListBox with three columns. These columns should be filled with values from column A to C.
It's fine this far, and I can figure out how to get the values but with massive code to get values from each cell if I know how many rows I have...

Now, as the rows with values in the columns will increase or even decrease I need something that can figure out how many rows of values I have and only add these rows.
Some kind of loop that adds row by row from all three columns?

Anyone have a solution for this?

 

[Norie]

Is it the same no of rows in each column?

If it is you can use this.

LastRow =  Range("A"&Rows.Count).End(xlUp).Row

Listbox1.List = Range("A1:C"& LastRow).Value

That would work even if there are less rows in some columns.

All you would need to change would be the column used to find the last row.

 

[Rob*]

Is it the same no of rows in each column?

If it is you can use this.

LastRow =  Range("A"&Rows.Count).End(xlUp).Row
 
Listbox1.List = Range("A1:C"& LastRow).Value

That would work even if there are less rows in some columns.

All you would need to change would be the column used to find the last row.

Yes it's always the same number of rows in each column.
I'll try this later when I find some time. 'll let you know how I do.

 

[Rob*]

Is it the same no of rows in each column?

If it is you can use this.

LastRow =  Range("A"&Rows.Count).End(xlUp).Row
 
Listbox1.List = Range("A1:C"& LastRow).Value

That would work even if there are less rows in some columns.

All you would need to change would be the column used to find the last row.

Today is the first day back at office so I could try this out. It's works perfect. Thanks a thousand times.

 

[Rob*]

I have a follow up question.

What if the listbox is in a sheet and not in a UserForm?
Any way to get this beautiful little code to add the info to that listbox?

 

[Norie]

You could use basically the same idea, but it would need to be changed a bit.

Also, you would have to decide when you want the listbox to be populated.

eg workbook open, worksheet activate etc

 

[Rob*]

You could use basically the same idea, but it would need to be changed a bit.

Also, you would have to decide when you want the listbox to be populated.

eg workbook open, worksheet activate etc

But how do I know what listbox to populate? (If I have many)
I can't name it, can I?
Worksheet activate is likely the option I'll use.

 

[Norie]

They all have names, and if you don't like the names you can change them.

How many listboxes are there?

Also, how did you create them?

That's quite important as the exact code you need depends on whether it's an ActiveX control or a Forms control.

 

[diddi]

no need to activate worksheet. that just slows everything down. you need to provide for us a list of the names of your listboxes and which column on your sheet corresponds to which listbox

eg
ListBox1 - column A
ListBox2 - Column C

etc

 

[Norie]

diddi

What other event would you recommend to populate a listbox on a worksheet?