Delete a table row if a cell contains a certain string

Question

I would like a code which run through all tables in the different worksheets of my spreadsheet and deletes a row if it contains a the value LeaverName and PositionLeaver in column 1 and 2.

Also if it could return the name of the worksheet where the row has been deleted.

My code is the following atm:

Sub Leavers()

Dim LeaverName As String
LeaverName = InputBox("Enter name of the employee leaving in the following format (Surname, First Name)", "Adding New Joiner to Hub")
Dim PositionLeaver As String
Position = InputBox("Enter new joiner Position (A, C, SC, PC, MP, Partner, Admin, Analyst, Director)", "Assigning New Joiner to a position")
'Input Name and Position of the employeee leaving and stores it (Could be improved with user form...)


Dim tbl As ListObject
Dim sht As Worksheet
Dim MyTable As ListObject

'Loop through each sheet and table in the workbook
For Each sht In ThisWorkbook.Worksheets
    For Each tbl In sht.ListObjects 'loop through all tables
        'To omit certain tables you can do the below
        If tbl.Name <> "Table2" And tbl.Name <> "Table3" And tbl.Name <> "Table5" And tbl.Name <> "Table7" _
        And tbl.Name <> "Table9" And tbl.Name <> "Table11" And tbl.Name <> "Table13" And tbl.Name <> "Table15" Then ...

At this point, I am not too sure how to approache the problem.

Thanks Guys !

Just to be sure, you want to delete a row of the tables not rows of worksheets? So your different tables are on different worksheets? — Pierre44
– Pierre44, Commented Oct 23, 2018 at 10:01
Yes, I want to delete rows of the tables. I have tables on all the worksheets. — Bernard Dadie
– Bernard Dadie, Commented Oct 23, 2018 at 10:05

SJR · Accepted Answer · 2018-10-23 12:56:55Z

2

This appears to work. You just a need loop through each worksheet and each table (which you started) and then each row of the body of the table. I think Select Case works better here for your list of excluded tables.

Sub x()

Dim ws As Worksheet, t As ListObject, r As Long, b As Boolean

For Each ws In Worksheets
    For Each t In ws.ListObjects
        Select Case t.Name
            Case "Table2", "Table3", "Table5", "Table7", "Table9", "Table11", "Table13", "Table15"
                'do nothing
            Case Else
                For r = t.DataBodyRange.Rows.Count To 1 Step -1
                    If t.DataBodyRange(r, 1) = "LeaverName" And t.DataBodyRange(r, 2) = "PositionLeaver" Then
                        t.DataBodyRange(r, 1).EntireRow.Delete
                        b = True
                    End If
                Next r
        End Select
    Next t
Next ws

If not b Then
    MsgBox ("No employee named " & LeaverName & " with the position " & PositionLeaver & _
                        " could be found." & vbNewLine & vbNewLine & "Double check the details and try again using the correct format.")
End If

End Sub

edited Oct 23, 2018 at 12:56

answered Oct 23, 2018 at 10:08

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Bernard Dadie Over a year ago

I havr posted an issue below :/

SJR Over a year ago

Do you have a table with no rows of data left in it? Not sure what you mean by the message box question?

Bernard Dadie Over a year ago

For the message box, I want a message saying "could not find" if there are no table with PositionLeaver and LeaverName

SJR Over a year ago

Do you mean if there isn't a single table in the whole workbook or just the sheet or just the table? I've amended the code above to give the message box if none of the tables in the workbook have those values.

Collectives™ on Stack Overflow

Delete a table row if a cell contains a certain string

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related