VBA - Delete Row in Table based on TextBox (Userform) Value

[sprigelz]

Hello,

I am trying to write a VBA script that will find a specific row in a table and delete it. The script should look at the value in a userform, search the table and delete that specific row.

Userform - RemEmpValidate
TextBox - TextBox2
Table - "Employees" on Worksheet "Employee List"

Textbox2 contains a Last Name value which will match with Table - Employees[Last Name] (Column C)

Can anyone help?

 

[sprigelz]

I attempted to write something like the below, but this doesn't seem to do anything

This just seems to give me an Object Required error.

Dim k As Integer
Dim tbl As Integer
Set tbl = Worksheets("Employee List").ListObjects("Employees").Range.Rows.Count

For k = tbl To 1 Step -1
If Cells(k, 12).Value = EmpID.Value Then
Cells(k, 1).EntireRow.Delete
End If
Next k

 

[sprigelz]

Any VBA experts able to assist by chance?

 

[Alex Blakenburg]

If I have my code in a standard module, I would call my module using something like this:

Private Sub CommandButton1_Click()
    Call DeleteEmployeeID(Me.TextBox2)
End Sub

And my deletion code would be something like this:
(where 12 is the column in the table)

Sub DeleteEmployeeID(sEmp As String)

    Dim k As Long
    Dim tbl As ListObject
    Dim tblNoOfRows As Long
    
    Set tbl = Worksheets("Employee List").ListObjects("Employees")
    tblNoOfRows = tbl.DataBodyRange.Rows.Count
    
    For k = tblNoOfRows To 1 Step -1
        If tbl.DataBodyRange.Cells(k, 12).Value = sEmp Then
            tbl.ListRows(k).Delete
        End If
    Next k

End Sub

 

[sprigelz]

@Alex Blakenburg This works beautifully. Thank you!!

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.