I am having trouble with how to put the data from the range with multiple columns and rows to a listbox.
Assume I have a range rng which multiple columns and rows I tried:
If I tried addItem rng(i,j) then everything would be in 1 column.
I also tried .list but it did not work either.
Is this what you are trying?
Option Explicit
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim rng As Range
Dim i As Long, j As Long, rw As Long
Dim Myarray() As String
'~~> Change your sheetname here
Set ws = Sheets("Sheet1")
'~~> Set you relevant range here
Set rng = ws.Range("A1:E5")
With Me.ListBox1
.Clear
.ColumnHeads = False
.ColumnCount = rng.Columns.Count
ReDim Myarray(rng.Rows.Count, rng.Columns.Count)
rw = 0
For i = 1 To rng.Rows.Count
For j = 0 To rng.Columns.Count
Myarray(rw, j) = rng.Cells(i, j + 1)
Next
rw = rw + 1
Next
.List = Myarray
'~~> Set the widths of the column here. Ex: For 5 Columns
'~~> Change as Applicable
.ColumnWidths = "50;50;50;50;50"
.TopIndex = 0
End With
End Sub
SNAPSHOT
I am assuming you want to populate 3 columns
Dim currRange As Range
Dim i As Integer
With Selection
For Each currRange In Range("yourRange")
i = i + 1
If i = 1 Then .AddItem cell.Value
If i = 2 Then .List(.ListCount - 1, 1) = "1"
If i = 3 Then
.List(.ListCount - 1, 2) = cell.Offset(0, 2).Value
i = 0
End If
Next
End With
I am assuming you have 3 columns.
You can use foreach to add items to the list box
ActiveSheet.Shapes("lstSample").Select
Dim currRange As Range
With Selection
For Each currRange In Range("yourRange")
.AddItem currRange.Value
Next
End With
For each itself iterates through each row and column in your range.