Delete Selected Row in UserForm ListBox linked to a Table

[PassSystems]

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:

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

 

[DanteAmor]

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:

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

 

[dmt32]

Hi,
Try disconnecting the Rowsource before the deletion & re-connect when completed

Untested but see if following does what you want

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

 

[PassSystems]

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).

 

[NoSparks]

Use list rather than rowsource to populate the listbox.

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

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

 

[PassSystems]

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.

 

[NoSparks]

You're welcome, glad I could assist & thanks for the feedback.