VBA Excel Populate ListBox with multiple columns

Question

This may be a cheap question for some but I'm totally confused on how to populate my listbox.

Using this line I can populate the listbox as shown below:
ListBox1.List = Sheets("Sheet1").Cells(1, 1).CurrentRegion.Value
or

Dim rngName As Range
Dim ws As Worksheet
Dim i As Integer
Set ws = Worksheets("Sheet1")
    For i = 1 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row Step 1
        If ws.Cells(i, 1).Value <> vbNullString Then Me.ListBox1.AddItem 
        ws.Cells(i, 1).Value
Next i

Below is the data I'm planning to use to populate the list box and is progressive. Only the column has the fix count.

Someone please enlighten me on how to populate a list box adapative to multiple columns and rows using FOR LOOP as shown in my code above. Any help appreciated. Thanks.

Have you seen [THIS](https://stackoverflow.com/questions/10763310/how-to-populate-data-from-a-range-multiple-rows-and-columns-to-listbox-with-vb) — Siddharth Rout, Nov 28 '17 at 10:05
And for non contiguous range see [THIS](https://stackoverflow.com/questions/10621758/excel-vba-multicollumn-listbox-add-non-contiguous-range) — Siddharth Rout, Nov 28 '17 at 10:06
@SiddharthRout Those line includes declaring certain range. Is it possible to use the range of all cells that are not blank cell? — Serversta, Nov 28 '17 at 10:10
Also, if you have a lookup table as shown, don't you need only one drop down and the others auto-populate unless you want update functionality? — QHarr, Nov 28 '17 at 10:17
Because the data is progressive. It is not fixed. Everytime the user inputs data, it adds up to the listbox. Hope someone can understand my point :( — Serversta, Nov 28 '17 at 10:18
How to auto populate this listbox? My mind is clouded for hours on finding how to do this one including update functionality. — Serversta, Nov 28 '17 at 10:19
@MarkJaysonVillanueva `myArray = Range("A1:A" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeConstants).Value` will create an array. Using `ListBox1.List = myArray` you can populate listbox. — AntiDrondert, Nov 28 '17 at 10:20
@Rory Thank you for telling me that. Can you enlighten me to another approach on how to auto populate the listbox without using static named ranges? — Serversta, Nov 28 '17 at 10:20
@AntiDrondert 1 column only I guess? I'm stuck on populating list box inluding more than 1 column. — Serversta, Nov 28 '17 at 10:23
@AntiDrondert man can you help me? What you give may lead me where I should be heading. — Serversta, Nov 28 '17 at 10:32
@MarkJaysonVillanueva `Set myRange = Range("A1:A" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row)` , `ListBox1.ColumnCount = myRange.Columns.Count` , `myArray = myRange.SpecialCells(xlCellTypeConstants).Value` , `ListBox1.List = myArray` something like this.. I guess? I didn't test it though. If list turns up to be sideways (for some reason), use `Application.Transpose(myArray)` before assigning it to listbox's list. — AntiDrondert, Nov 28 '17 at 10:34
@MarkJaysonVillanueva Acualy meant `Set myRange = Range("A1:C" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row)`, forgot it isn't just one column anymore. — AntiDrondert, Nov 28 '17 at 10:44
@AntiDrondert I'm getting run-time error 91, object variable or with block variable not set — Serversta, Nov 28 '17 at 10:44
@MarkJaysonVillanueva duh, which line? Is it `Set myRange = Range("A1:C" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row)` or the one with `ListBox1`? — AntiDrondert, Nov 28 '17 at 10:52
@AntiDrondert yes. I put break on that line but I declare that already. — Serversta, Nov 28 '17 at 10:54
@Serversta Does it throw an error nonetheless? Or did you fix it already? Does the code work as intended? — AntiDrondert, Nov 28 '17 at 10:58
@AntiDrondert It's throwing error still the same. I declared all the variable from the code you give me. — Serversta, Nov 28 '17 at 11:00
I said dynamic named range. It is dynamic not static. This would also allow for lookup. — QHarr, Nov 28 '17 at 11:24

T.M. · Accepted Answer · 2017-11-28T15:28:24.130

Methods

It's always better to loop through an array than a range - it's much faster.
It's even faster to create a variant data field array with a one liner instead of redimensioning a predeclared array and fill it in an extra loop as proposed by Siddharth Rout (though a good method :-) Note: The code below is based on his Approach referenced in the above comment just to demonstrate the difference.
Fill ListBox1.List with the array (same method, but reverse direction).

Code

Private Sub CommandButton1_Click()
' Purpose:  fill listbox with range values after clicking on CommandButton1
'           (code could be applied to UserForm_Initialize(), too)
' Note:     based on @Siddharth-Rout 's proposal at https://stackoverflow.com/questions/10763310/how-to-populate-data-from-a-range-multiple-rows-and-columns-to-listbox-with-vb
'           but creating a variant data field array directly from range in a one liner
'           (instead of filling a redimensioned array with range values in a loop)
Dim ws      As Worksheet
Dim rng     As Range
Dim MyArray                 ' variant, receives one based 2-dim data field array
'~~> Change your sheetname here
Set ws = Sheets("Sheet1")

'~~> Set you relevant range here
Set rng = ws.Range("A1:C" & ws.Range("A" & ws.Rows.Count).End(xlUp).Row)

With Me.ListBox1
    .Clear
    .ColumnHeads = False
    .ColumnCount = rng.Columns.Count

    '~~> create a one based 2-dim datafield array
     MyArray = rng

    '~~> fill listbox with array values
    .List = MyArray

    '~~> Set the widths of the column here. Ex: For 5 Columns
    '~~> Change as Applicable
    .ColumnWidths = "50;50;50"
    .TopIndex = 0
End With
End Sub

Additional hints

Another advantage of the array method - it overcomes the built-in limitation of only 10 columns when using the .AddItem method.
Furthermore, keep in mind that listbox indexing is zero based, so for example you get the e-mail address (column 3, index 2) of your first item row (index 0) via ListBox1.List(0, 2), whereas the data field array becomes automatically a one based 2-dim array.
You aren't restricted to use the .List method to get Information out of the listbox, you can reverse the row - column order by using ListBox1.Column" or even create a new array out of it, which remains a 2-dim object, even if there is only ONE item (note: theApplication.Transpose` method would redim a 2 dimensional array with only one row to a 1-dim array).
A last point: you can easily dump back again the whole listbox to an Excel sheet via rng = ListBox1.List, but take care to define the correct range.

I am grateful for the positive learning environment you provided me with. I learned that the approach you'd given me was better! — Serversta, Nov 28 '17 at 12:23

score 4 · Answer 2 · answered Nov 28 '17 at 11:20

How about this:

Sub foo()
Dim rngName As Range
Dim ws As Worksheet
Dim i As Integer
Set ws = Worksheets("Sheet1")
ListBox1.Clear
ListBox1.columnCount = 3
Dim LastRow As Long
LastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    For i = 1 To LastRow
        If ws.Cells(i, 1).Value <> vbNullString Then ListBox1.AddItem ws.Cells(i, 1).Value
        If ws.Cells(i, 2).Value <> vbNullString Then ListBox1.List(i - 1, 1) = ws.Cells(i, 2).Value
        If ws.Cells(i, 3).Value <> vbNullString Then ListBox1.List(i - 1, 2) = ws.Cells(i, 3).Value
    Next i
End Sub

Thank you for taking the trouble to help me. I do appreciate it! — Serversta, Nov 28 '17 at 12:02

VBA Excel Populate ListBox with multiple columns

2 Answers2

Linked