dss28
Board Regular
- Joined
- Sep 3, 2020
- Messages
- 165
- Office Version
- 2007
- Platform
- Windows
At present I have a code to select and delete one row from a listbox and the corresponding sheet.
However i need to modify it to select multiple rows from the listbox and delete them from listbox as well as the corresponding data rows from the sheet.
please help to modify:
i am using the following code to delete one row at a time:
However i need to modify it to select multiple rows from the listbox and delete them from listbox as well as the corresponding data rows from the sheet.
please help to modify:
i am using the following code to delete one row at a time:
VBA Code:
Private Sub UserForm_Initialize() 'load listbox in userform6 with sheet row data equivalent to rows in userform3 listbox for deletion
Dim LR As Long
Dim HowManyRows As Long
HowManyRows = UserForm3.ListBox1.List(UserForm3.ListBox1.ListIndex, 4) 'qty.
With Worksheets("Data")
LR = .Cells(.Rows.Count, 1).End(xlUp).row
Me.ListBox1.ColumnCount = 6
Me.ListBox1.ColumnWidths = "0,80,0,0,150,0"
Me.ListBox1.RowSource = .Range(.Cells(LR - HowManyRows + 1, 1), .Cells(LR, 8)).Address
End With
End Sub
Private Sub CommandButton1_Click() ' delete row
If Selected_List = 0 Then
MsgBox "No row is selected.", vbOKOnly + vbInformation, "Delete"
Exit Sub
End If
Dim i As VbMsgBoxResult
i = MsgBox("Do you want to delete the selected record?", vbYesNo + vbQuestion, "Delete")
If i = vbNo Then Exit Sub
Dim row As Long
row = Me.ListBox1.List(Me.ListBox1.ListIndex, 0) + 1
ThisWorkbook.Sheets("Data").Rows(row).Delete
' MsgBox "Selected records has been deleted successfully.", vbOKOnly + vbInformation, "Delete"
Unload Userform6
End Sub
Function Selected_List() As Long
Dim i As Long
Selected_List = 0
If UserForm6.ListBox1.ListCount < 1 Then Exit Function
For i = 0 To UserForm6.ListBox1.ListCount - 1
If UserForm6.ListBox1.Selected(i) = True Then
Selected_List = i + 1
Exit For
End If
Next i
End Function