Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill a listbox with data from variable range
Hi all,
I'm trying to add items to a listbox, from a source that changes. The values start from range B4. There can be as little as 5 cells to 36 cells of data that i need to add into the listbox. I've tried to define the range (i.e. rng = sheets("Data").range("B4").Selection.End(xlDown)) and get the listbox to pick the values up from that range; and have tried a looping statement to pick up cells from B4 until it hits a blank cell. The sheet with the data is hidden, and im trying not to use any select or activate commands to fill the listbox. Can anyone please help. Cheers. Al |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill a listbox with data from variable range
Thanks William.
Setting up the range line was where i was having trouble. Your's works fantastically. Thanks heaps. Al. "William" wrote: Hi Al Assuming the listbox is on a UserForm you could try something like the following when you open the UserForm..... Private Sub UserForm_Initialize() Dim r As Range, c As Range With Sheets("Data") Set r = .Range(.Range("B4"), .Range("B" & Rows.Count).End(xlUp)) For Each c In r ListBox1.AddItem c Next c End With End Sub -- XL2002 Regards William "Al" wrote in message ... | Hi all, | | I'm trying to add items to a listbox, from a source that changes. | The values start from range B4. There can be as little as 5 cells to 36 | cells of data that i need to add into the listbox. | | I've tried to define the range (i.e. rng = | sheets("Data").range("B4").Selection.End(xlDown)) and get the listbox to pick | the values up from that range; | and have tried a looping statement to pick up cells from B4 until it hits a | blank cell. | | The sheet with the data is hidden, and im trying not to use any select or | activate commands to fill the listbox. | | Can anyone please help. | Cheers. | Al | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range of data is variable... | Charts and Charting in Excel | |||
Setting up a validation of data listbox to provide the unique items within a range | Excel Worksheet Functions | |||
Variable data range help | Charts and Charting in Excel | |||
Set Data Range by Variable | Excel Discussion (Misc queries) | |||
.ListFillRange as Variable ? in Listbox | Excel Programming |