Delete Selected Row in UserForm ListBox linked to a Table

PassSystems

New Member
Joined
Sep 19, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
I am creating a spreadsheet for my business and have hit a snag. I have a UserForm (ApplianceDataForm) with a ListBox (ListTechnicianInfo) and I am trying to set up a row delete button. The ListBox has the RowSorce linked to a Table (Table_Technician) which is located in a worksheet named “Appliance Data”. This worksheet has several other tables in it.

I have set up a delete button in another UserForm ListBox that has the RowSource linked to a table that is the only one in the Worksheet. The following works very well:

VBA Code:
Private Sub ButtonDeleteRow_Click()

Application.ScreenUpdating = False

Dim i As Integer

   If MsgBox("Are you sure you want to delete this data row?", vbYesNo + vbQuestion, "Delete Row?") = vbYes Then
   
         Worksheets("Appliance Test Data").Select
          For i = 1 To Range("A100000").End(xlUp).Row
          If Cells(i, 1) = TestDataList.List(TestDataList.ListIndex) Then
          Rows(i).Select
          Selection.Delete

   End If
   Next i
   End If

Application.ScreenUpdating = True

End Sub

I am however having problems with the second scenario. I assume it is the range and the subsequent deletion that I am having issues with.

Is there any help you can give me to stop me bashing my head against the desk… LOL
 
Last edited by a moderator:

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
I am however having problems with the second scenario. I assume it is the range and the subsequent deletion that I am having issues with.

In column A is there more than one record with the same data?
That is, you want to delete multiple records.
If so, try the following:

VBA Code:
Private Sub ButtonDeleteRow_Click()
  Dim i As Integer
  Application.ScreenUpdating = False
  
  If MsgBox("Are you sure you want to delete this data row?", vbYesNo + vbQuestion, "Delete Row?") = vbYes Then
    Worksheets("Appliance Test Data").Select
    For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
      If Cells(i, 1) = TestDataList.List(TestDataList.ListIndex) Then
        Rows(i).Delete
      End If
    Next i
  End If
  Application.ScreenUpdating = True
End Sub
 
Upvote 0
Hi,
Try disconnecting the Rowsource before the deletion & re-connect when completed

Untested but see if following does what you want

VBA Code:
Private Sub ButtonDeleteRow_Click()
    Dim rng             As Range
    Dim RecordRow       As String
    
    With ThisWorkbook.Worksheets("Appliance Test Data")
        Set rng = .Range(.Range("A2"), .Range("A" & .Rows.Count).End(xlUp))
    End With
    
'record row
    RecordRow = TestDataList.ListIndex + 1
    
        If MsgBox("Are you sure you want To delete this data row?", 36, "Delete Row?") = vbYes Then
            With TestDataList
'disconnect rowsource
                .RowSource = ""
'delete row
                rng.Rows(RecordRow).EntireRow.Delete
'resize range
                Set rng = rng.Resize(rng.Rows.Count)
're-connect rowsource
                .RowSource = rng.Address
                .ListIndex = RecordRow - 1
            End With
        End If
    
End Sub

I have assumed, row 1 is a header row

Dave
 
Upvote 0
Hi Guys

Many thanks for your quick reply. Is there any way that i can set the range to a table? The table is a single column located in column W (W9 is the header).
 
Upvote 0
Use list rather than rowsource to populate the listbox.
VBA Code:
Private Sub UserForm_Initialize()
Me.ListTechnicianInfo.List = Sheets("Appliance Data").ListObjects("Table_Technician").DataBodyRange.Value
End Sub

Having more than one table on the sheet I'm guessing it would be better to only remove the row from the table rather than the entire row across the whole sheet
VBA Code:
Private Sub DeleteButton_Click()
    Dim oLo As Object
    Dim tech As String, fndTech As Range

If Me.ListTechnicianInfo.ListIndex <> -1 Then
    tech = Me.ListTechnicianInfo.Value
    Set oLo = Sheets("Appliance Data").ListObjects("Table_Technician")
    
    With oLo.ListColumns(1).Range
        Set fndTech = .Find(What:=tech, LookIn:=xlValues, LookAt:=xlWhole, _
            SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    End With
    
    If Not fndTech Is Nothing Then
        ' only delete row from table
        oLo.ListRows(fndTech.Row - oLo.HeaderRowRange.Row).Range.Delete
    End If
    
    Me.ListTechnicianInfo.List = oLo.DataBodyRange.Value
End If
End Sub


Hope that helps
 
Upvote 0
OMG NoSparks you are a lifesaver. It works perfectly. It has also given me ideas for the coding of other areas of the spreadsheet that will make it so much more efficient.

Thank you so much.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top