How do I populate a listbox in excel with a matrix?

user_11995192 0
Viewed 96

I'm trying to populate a listbox in excel with information that is a "matrix"(basically I have some data in the rows and some in the columns. I'm using the code below but I have 2 problems.

Dim Rows As Integer
Dim Kolumns As Integer
Dim Start As Range

Set Start = Sheets("sheet1").Range("B2")
Start.Select

Rows = 10
Kolumns = 5

For i = 1 To Rows
    For j = 1 To Kolumns
    
    ListBox1.AddItem
    ListBox1.List(i - 1, j - 1) = ActiveCell.Offset(i - 1, j - 1).Value
    
    Next j
Next i

The first problem is that the rows is being doubled, if I write "rows = 10" I then get 20 rows. (The columns work fine).

The second problem is my "select". I know that it's not the best option to use but I don't know how to avoid it?

matrix vba excel listbox
3 Answers

As already written in the comment, you can use the range variable (in your case Start) directly to access the content of the cells.

The reason that you get 20 instead of 10 entries in the listbox is that you have the AddItem within the inner loop, and that is executed 10*2 = 20 times. You need to move it into the outer loop so that only one item per row is created:

Const rowCount = 10
Const colCount = 2

Dim Start As Range
Set Start = ThisWorkbook.Sheets("sheet1").Range("B2")

Dim i As Long, j As Long
For i = 1 To rowCount
    ListBox1.AddItem
    For j = 1 To colCount
        ListBox1.List(i - 1, j - 1) = Start.Offset(i - 1, j - 1).Value
    Next j
Next i
user_75997980

Try the next way, please. No need of any iteration. A ListBox has a List property which accept an array:

Sub loadListBox()
 Dim sh As Worksheet, iRows As Long, Kolumns As Long, Start As Range, arr
 
 Set sh = Sheets("Sheet1")

 Set Start = sh.Range("B2")
 iRows = 10:  Kolumns = 5

 arr = sh.Range(Start, Start.Offset(iRows, Kolumns)).Value

 With ListBox1
    .Clear
    .ColumnCount = Kolumns + 1
    .list = arr
 End With
End Sub
user_22333080

Populate List Box

  • More efficient is not using a loop at all.
  • Note the use of the ColumnCount property which is used to ensure the right number of columns.

The Code

Option Explicit

Sub populateListBox()
    
    Const rCount As Long = 10
    Const cCount  As Long = 5
    
    Dim cel As Range: Set cel = Sheet1.Range("B2")
    
    With Sheet1.ListBox1
        .ColumnCount = cCount
        .List = cel.Resize(rCount, cCount).Value
    End With

End Sub
user_98140690
Related