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:

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,170
Office Version
  1. 2019
Platform
  1. Windows
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
 

PassSystems

New Member
Joined
Sep 19, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 15, 2013
Messages
998
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
 

PassSystems

New Member
Joined
Sep 19, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 15, 2013
Messages
998
Office Version
  1. 2010
Platform
  1. Windows
You're welcome, glad I could assist & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,745
Messages
5,543,959
Members
410,586
Latest member
acadavid86
Top