CODEWITHSUNDEEP
excelvba
Bernard Dadie
Bernard Dadie

Reputation: 3

Delete a table row if a cell contains a certain string

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 !

Upvotes: 0

Views: 76

Answers (1)

SJR
SJR

Reputation: 23081

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

Upvotes: 2

Related Questions