Code for Deleting Multiple Rows Selected in ListBox (VBA)

ganesh_r1988

New Member
Joined
Mar 12, 2019
Messages
3
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.
ZoneRegion
NorthN1
NorthN2
NorthN3
NorthN4
SouthS1
SouthS2
SouthS3
SouthS4
EastE1
EastE2

<tbody>
</tbody>


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.

Code:
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.

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-

Code:
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

New Member
Joined
Mar 12, 2019
Messages
3
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,523
Messages
5,529,334
Members
409,863
Latest member
stacy09
Top