How to delete multiple selected rows of data from a listbox that uses a listobject as the rowsource in Excel VBA?

Question

I have a UserForm with a listbox using a listobject as the rowsource.

I want to select multiple rows of data from the listbox, delete the corresponding data from the listobject, and then update the listbox.

Private Sub Delete_Click()

'Option Explicit is enabled

    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim myListBox As MSForms.listbox
    Dim i As Long
    
    Set ws = ThisWorkbook.Worksheets("Data")
    Set tbl = ws.ListObjects("Table1")
    Set myListBox = Me.listbox
    
    With myListBox
        For i = .ListCount - 1 To 0 Step -1
            If .Selected(i) Then
                tbl.ListRows(i + 1).Delete
            End If
        Next i
    End With
    
    myListBox.RowSource = ""
    myListBox.RowSource = tbl
End Sub

I had code for deleting one row. This is for trying to delete multiple rows.

I can select multiple rows of data from the listbox but it will only delete the bottommost row of data, regardless of order of selection.

Answer 1

I made a mistake in the code in my comment which is this line ListBox1.RowSource = .Range.Address. It should be ListBox1.RowSource = tbl.Range.Address. And better, in case the sheet Data is not active : ListBox1.RowSource = ws.Name & "!" & tbl.Range.Address

To test the code below, create a userform with one Listbox and one command button.

Dim ws As Worksheet
Dim tbl As ListObject

Private Sub UserForm_Initialize()
Set ws = ThisWorkbook.Worksheets("Data")
Set tbl = ws.ListObjects("Table1")
    With ListBox1
        .ColumnCount = 3
        .RowSource = ws.Name & "!" & tbl.Range.Address
        .ColumnWidths = "35,35,35"
        .MultiSelect = fmMultiSelectExtended
    End With
End Sub

Private Sub CommandButton1_Click()
Dim rgU As Range: Dim i As Integer
If Not tbl.DataBodyRange Is Nothing Then
    With ListBox1
        For i = 0 To .ListCount - 1
            If .Selected(i) And i <> 0 Then If rgU Is Nothing Then Set rgU = tbl.ListRows(i).Range Else Set rgU = Union(rgU, tbl.ListRows(i).Range)
        Next
        rgU.Delete: .RowSource = ws.Name & "!" & tbl.Range.Address
    End With
End If
End Sub

The row source for the listbox use the table address, but in my comment I forgot to include the name of the sheet (which is sheet Data) in case sheet Data is not active when showing the userform. Now in the code above I include the sheet name (ws.name).

Since this is just a test sub, you need sheet Data is active before showing the userform, in order to see if it delete a correct row in the table.