Loop through listbox selections

[tomhow]

I have a userform with a listbox with the Multiselect set as SelectExtended. My aim is for the user to select multiple items from the listbox and on execution the code below will find each selection in the activesheet and delete that row. My code only deletes the first selection. What am I missing.

[FONT=Calibri][SIZE=3][COLOR=#000000]Private SubCommandButton1_Click()[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim ExStock As Range[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim Name As String[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    Dim lItem As Long[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]    For lItem = 0 To ListBox1.ListCount - 1[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]                If ListBox1.Selected(lItem) = True Then[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]    With ActiveSheet.Range("B1:B135")[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        Name = ListBox1.List(lItem)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        Set ExStock = .Find(what:=Name,LookIn:=xlValues)[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]        If Not ExStock Is Nothing ThenExStock.Cells.EntireRow.Delete[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]    End With[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]End If[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000]Next[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=3][COLOR=#000000] Me.Hide[/COLOR][/SIZE][/FONT]

[FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT]

 

[yujin]

I think your code is good enough except for a missing space between "Then" and "ExStock.Cells.EntireRow.Delete" on line 14.

 

[Fluff]

When you say

My code only deletes the first selection.

Do you mean it only deletes the 1st selected value in the listbox, or do you mean it only deletes 1 row for each selected value in the listbox?

 

[tomhow]

Problem solved, At 3.00 o'clock in the morning, I realised that the rowsource for the listbox was on the same sheet as the data rows I was deleting. When the macro deleted the first row it also skewed the listbox rowsource. Dumb; Dumb; Dumb.
Thanks for your interest.
Tomhow

 

[Fluff]

Glad you got it sorted & thanks for the feedback