Code for Deleting Multiple Rows Selected in ListBox (VBA)

Question

Currently I am into a project where I was stucked writing code for the below mentioned activity.

I have created an userform with a multiselect Listbox and a Command Button

a) ListBox populates the items from a table source with 2 columns.

Zone    Region
North   N1
North   N2
North   N3
North   N4
South   S1
South   S2
South   S3
South   S4
East    E1
East    E2

b) Command Button is for deleting the items selected in the listbox.

Now Once I click the Delete Command Button , the selected rows should get deleted in the Original Table Source.

Issue

The issue I am facing is even when I select 2 or more rows in the listbox , only the last selected row is getting deleted.

Private Sub Cmd_Del_Click()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets("Sheet1")
    Dim i As Long


    For i = Me.LB_ZoneRegion.ListCount - 1 To 0 Step -1
        If Me.LB_ZoneRegion.Selected(i) = True Then
            sh.Range("A" & i + 2 & ":B" & i + 2).Select
            Selection.Delete
        End If
    Next i
    Call UserForm_Initialize
End Sub

Private Sub UserForm_Initialize()
    On Error Resume Next
    With Me.LB_ZoneRegion
        .Clear
        .ColumnCount = 2
        .ColumnHeads = True
        .ColumnWidths = "40;50"
        .RowSource = "ZoneRegion"
        .MultiSelect = fmMultiSelectMulti
    End With
End Sub

Click the below link to download the working file. https://drive.google.com/open?id=1P5wiW6WVFAVQBgixPuA7gqyacR1aktvi

Please help me out in this regard.

Have you tried stepping through your code using F8 on your keyboard and keeping an eye on your Me.LB_ZoneRegion.Selected(i) value through each iteration of your loop in the Locals window of the VBE? By stepping through the code you can also check that your code is functioning as expected (for example the loop isn't exiting earlier than expected...etc). — Samuel Everson
@Ganesh - posted an approach using the Union function to solve your issue. — T.M.

T.M. T.M. · Accepted Answer · 2019-03-19T21:24:17

Using Union to avoid repeated deletes

Using the Union function you can add all rows to be deleted to a range object (e.g. DelRng) and finally delete them via one single code line DelRng.Delete.

Example code close to OP

Private Sub Cmd_Del_Click()
    Dim sh As Worksheet
    Dim DelRng As Range
    Set sh = ThisWorkbook.Worksheets("Sheet1")
    Dim i As Long
    For i = 0 to Me.LB_ZoneRegion.ListCount - 1    ' no more need to loop backwards
        If Me.LB_ZoneRegion.Selected(i) = True Then
            If DelRng Is Nothing Then
                Set DelRng = sh.Range("A" & i + 2 & ":B" & i + 2)
            Else
                Set DelRng = Union(DelRng, sh.Range("A" & i + 2 & ":B" & i + 2))
            End If
        End If
    Next i
    Application.ScreenUpdating = False
    If Not DelRng Is Nothing Then DelRng.Delete    ' delete chosen rows (if any)
    Application.ScreenUpdating = True


End Sub

Note

You should also provide for the case that the user deletes the whole named range.

Code for Deleting Multiple Rows Selected in ListBox (VBA)

1 Answers