VBA Excel 用多列填充列表框

VBA Excel Populate ListBox with multiple columns

提问by Serversta

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

使用这一行，我可以填充列表框，如下所示：
ListBox1.List = Sheets("Sheet1").Cells(1, 1).CurrentRegion.Value
或

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 LOOPas shown in my code above. Any help appreciated. Thanks.

有人请教我如何使用FOR LOOP填充适合多列和多行的列表框，如我上面的代码所示。任何帮助表示赞赏。谢谢。

回答by T.M.

Methods

方法

1. It's always better to loop through an array than a range - it's much faster.
2. It's even faster to create a variant data field array with a one linerinstead 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.
3. Fill ListBox1.Listwith the array (same method, but reverse direction).

1. 遍历数组总是比遍历范围要好——它要快得多。
2. 使用单行创建变体数据字段数组甚至更快，而不是重新定义预先声明的数组并将其填充到 Siddharth Rout 提出的额外循环中（尽管这是一个很好的方法 :-)注意：下面的代码基于他的方法在上面的评论中引用只是为了证明差异。
3. ListBox1.List用数组填充（相同的方法，但方向相反）。

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 overcomesthe built-in limitation of only 10 columnswhen using the .AddItemmethod.

• 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 .Listmethod 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.

• 阵列方法的另一个优点 - 它克服了使用该方法时只有10 列的内置限制.AddItem。

• 此外，请记住，列表框索引是基于 0 的，因此例如您通过 获得第一项行（索引 0）的电子邮件地址（第 3 列，索引 2）ListBox1.List(0, 2)，而数据字段数组自动变为基于一个二维数组。

• 您不限于使用该.List方法从列表框中获取信息，您可以通过使用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` 方法反转行 - 列顺序将只有一行的二维数组重新映射为一维数组）。

• 最后一点：您可以通过 轻松地将整个列表框再次转储到 Excel 工作表中rng = ListBox1.List，但请注意定义正确的范围。

回答by Xabier

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