Deleting Whole Rows From ListBox2

  • Thread starter Thread starter Randal W. Hozeski
  • Start date Start date
R

Randal W. Hozeski

Hello:

I have a UserForm that has a listbox in it that I use to verify
an entry has been made. I wrote a Sub to add a record to
the range that populates the ListBox, sorts it, and refreshes
it. The range name is "DBList." and it is stored on a sheet
name Sheet6!DBList.

The listbox is a multiple select type. I am having a bit of trouble
removing the selected items from the listbox, which would
entail the complete removal of that line within Sheet6!DBList
range.

Any assistance would be appreciated. Thanks -Randy-

..
 
Private Sub CommandButton1_Click()
Dim rng As Range
Dim i As Long
For i = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(i) Then
If rng Is Nothing Then
Set rng = Range("DBLIST").Cells(i + 1, 1)
Else
Set rng = Union(rng, Range("DBLIST").Cells(i + 1, 1))
End If
ListBox1.Selected(i) = False
End If
Next
If Not rng Is Nothing Then
ListBox1.RowSource = ""
rng.EntireRow.Delete Shift:=xlShiftUp
ListBox1.RowSource = "DBLIST"
End Sub
 
Back
Top