Excelguru Help Site - Fill MultiColumn Listbox With Worksheet Range

Introduction:
This article gives two methods to fill a multi column listbox on a userform with data from an Excel worksheet.

Pulling the worksheet range directly into a listbox
Pulling the data into an array first, then placing the array in the listbox

The intention of both of these pieces of code is to be used in a userform. Both are set up in the Userform_Intialize event, although only one or other should be used.

Versions Tested:
These routines were tested successfully running Office 2003, but shouldn't have any issue in Excel 97 or higher.

Pulling the worksheet range directly into a listbox

Code:

Private Sub UserForm_Initialize()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: To populate a multi-column listbox with data from
'               a worksheet range
    
    Dim lbtarget As MSForms.ListBox
    Dim rngSource As Range
    
    'Set reference to the range of data to be filled
    Set rngSource = Worksheets("Sheet1").Range("A2:C5")
    
    'Fill the listbox
    Set lbtarget = Me.ListBox1
    With lbtarget
        'Determine number of columns
        .ColumnCount = 3
        'Set column widths
        .ColumnWidths = "50;80;100"
        'Insert the range of data supplied
        .List = rngSource.Cells.Value
    End With
    
End Sub

Pulling the data into an array first, then placing the array in the listbox

Code:

Private Sub UserForm_Initialize()
'Author       : Ken Puls (www.excelguru.ca)
'Macro Purpose: To populate a multi-column listbox with data from
'               a worksheet range
    Dim lb As msforms.ListBox
    Dim rcArray() As Variant
    Dim lrw As Long, lcol As Long
    Dim rngTarget As Range
    
    'Define the range you want to use
    Set rngTarget = Worksheets("Sheet1").Range("A2:C5")
    
    'Set the boundaries of the array
    ReDim Preserve rcArray(1 To rngTarget.Rows.Count, 1 To rngTarget.Columns.Count)
    
    'Fill the array with data from the worksheet
    With rngTarget
        For lcol = 1 To .Columns.Count
            For lrw = 1 To .Rows.Count
                rcArray(lrw, lcol) = rngTarget.Cells(lrw, lcol)
            Next lrw
        Next lcol
    End With
    
    'Place the array in the listbox
    Set lb = Me.ListBox1
    With lb
        .ColumnCount = 3
        .ColumnWidths = "50;80;100"
        .List = rcArray
    End With
    
End Sub

Example File: