Code for Deleting Multiple Rows Selected in ListBox (VBA)

[ganesh_r1988]

Hi,


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


ListBox populate the Items from a Table Source with 2 Columns.
[TABLE="width: 129"]
<tbody>[TR]
[TD]Zone[/TD]
[TD]Region[/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD]N1[/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD]N2[/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD]N3[/TD]
[/TR]
[TR]
[TD]North[/TD]
[TD]N4[/TD]
[/TR]
[TR]
[TD]South[/TD]
[TD]S1[/TD]
[/TR]
[TR]
[TD]South[/TD]
[TD]S2[/TD]
[/TR]
[TR]
[TD]South[/TD]
[TD]S3[/TD]
[/TR]
[TR]
[TD]South[/TD]
[TD]S4[/TD]
[/TR]
[TR]
[TD]East[/TD]
[TD]E1[/TD]
[/TR]
[TR]
[TD]East[/TD]
[TD]E2[/TD]
[/TR]
</tbody>[/TABLE]


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.


For this I have written the below code, But 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.

Thanks in Advance

Regards,
Ganesh.
[TABLE="width: 129"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

Try this:-

Private [COLOR="Navy"]Sub[/COLOR] Cmd_Del_Click()
[COLOR="Navy"]Dim[/COLOR] sh [COLOR="Navy"]As[/COLOR] Worksheet
[COLOR="Navy"]Set[/COLOR] sh = ThisWorkbook.Worksheets("Sheet1")
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]With[/COLOR] sh
[COLOR="Navy"]For[/COLOR] i = Me.LB_ZoneRegion.ListCount - 1 To 0 [COLOR="Navy"]Step[/COLOR] -1
    [COLOR="Navy"]If[/COLOR] Me.LB_ZoneRegion.Selected(i) = True [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]Set[/COLOR] nRng = .Range("A" & i + 1)
        [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, .Range("A" & i + 1))
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] i
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nRng.Delete shift:=xlUp
Call UserForm_Initialize
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[ganesh_r1988]

Hi, Thanks a lot for your help . Can you please explain how Union function works and how nRng will delete the group of ranges in one stretch.
Regards,
Ganesh.

 

[MickG]

If you step through your code you will see that after the first deletion the listbox resets itself and you original selection is lost.
By creating a Range object "nRng" and Loading the selected data into it , you are able to delete the entire selected rows in one go.

NB: I have not altered the "step -1" in the Loop, but this is not needed when creating a new "nRng" object.

You can get the Basic method for creating a Range object using union from the code, but if you Google "VBA + Union" I think you will find plenty of information.

 

[ganesh_r1988]

Hi, Thanks a lot for your valuable update. Will do as you instructed.

 

[MickG]

You're welcome