Transfer Multi-Select ListBox To Range Of Cells

Transfer Selected Rows Of Multi-Select & Multi-Column ListBox To Range Of Cells On Worksheet

The VBA code below will transfer all selected rows and columns of a multi-selected listbox. On loading the user form, the ColumnCount Property of the ListBox is set to as many columns as the RowSource Property.

Download working example

Private Sub UserForm_Initialize()
    'Set ListBox ColumnCount to the same as RowSource
    ListBox1.ColumnCount = Range(ListBox1.RowSource).Columns.Count
End Sub




Private Sub TransferButton_Click()
 Dim lItem As Long, lRows As Long, lCols As Long
 Dim bSelected As Boolean
 Dim lColLoop As Long, lTransferRow As Long
    
 'Pass row & column count to variables
 'Less 1 as "Count" starts at zero
 lRows = ListBox1.ListCount - 1
 lCols = ListBox1.ColumnCount - 1
 
    'Ensure they have at least 1 row selected
    For lItem = 0 To lRows
        'At least 1 row selected
        If ListBox1.Selected(lItem) = True Then
            'Boolean flag
            bSelected = True
            'Exit for loop
            Exit For
        End If
    Next
     
    'At least 1 row selected
    If bSelected = True Then
        With Sheet1.Range("D1", Sheet1.Cells(lRows + 1, 4 + lCols)) 'Transfer to range
            .Cells.Clear 'Clear transfer range
            For lItem = 0 To lRows
                If ListBox1.Selected(lItem) = True Then 'Row selected
                  'Increment variable for row transfer range
                  lTransferRow = lTransferRow + 1
                    'Loop through columns of selected row
                    For lColLoop = 0 To lCols
                       'Transfer selected row to relevant row of transfer range
                       .Cells(lTransferRow, lColLoop + 1) = ListBox1.List(lItem, lColLoop)
                        'Uncheck selected row
                        ListBox1.Selected(lItem) = False
                    Next lColLoop
                End If
            Next
        End With
         Unload Me
    Else ' NO listbox row chosen
         MsgBox "Nothing chosen", vbCritical
    End If

End Sub