VBA - Delete only last row of table containing text

Question

I’m having a very specific question. I hope you can help me

I have a spreadsheet that contains a table called "Activities". I have some form controls to fill the same but I emerged the need to insert and delete rows from this table without interfering with the cells next to it. Filling a new row of the table without affecting the cells on the side is no problem, I’ve already done that. The problem is to delete the column row that contains the added value (only the last one). Follow images to illustrate what I intend to do.

By clicking on the + button of "Answered Phone Calls", it adds a row in the "Activities" table with the text "Phone Service".

I wish I could do the same thing with the button less, when clicking on it, the last line containing "Phone Service" is deleted (only the last one inside the table), but in a way not to interfere with the cells with the rectangle in blue.

Is there a way? As you can see, I already have a control that can delete the last row of the table, I use this code to do this:

Private Sub RemoveLinha_Click()
Dim i As Integer
i = ActiveSheet.ListObjects("Atividades").Range.Rows.Count
If i > 2 Then
    Range("D" & i).ListObject.ListRows(i - 1).Delete
End If
End Sub

However, I don’t know how to identify only the last value containing the specific text for each button unless I add.

Answer 1

Like the Listobject is being used, follow the code to delete the last line.

Last row of table with value

This is a code that creates an array of table values and performs a reverse loop (from the last line to the first) in column 2 looking for the value of "Answered Phone" and find yourself, remove the line and exit the loop.

Code

Dim ws As Worksheet
Dim tbl As ListObject
Dim i As Long
Dim matriz As Variant
Set ws = ActiveSheet                         'ou para declarar com o nome da planilha: ThisWorkbook.Sheets("Agosto")
Set tbl = ws.ListObjects("Nome_da_Tabela")   'No seu caso: .ListObjects("Atividades")
With tbl
    matriz = .DataBodyRange                   'Cria matriz
    For i = UBound(matriz) To LBound(matriz) Step -1
        If matriz(i, 2) = "Telefonemas Atendidos" Then
            .ListRows(i).Delete
            Exit For
        End If
    Next i
End With

Last Line

Code

Dim ws As Worksheet
Dim tbl As ListObject

Set ws = ActiveSheet                         'ou para declarar com o nome da planilha: ThisWorkbook.Sheets("Agosto")
Set tbl = ws.ListObjects("Nome_da_Tabela") 'No seu caso: .ListObjects("Atividades")
With tbl
    .ListRows(.DataBodyRange.Rows.Count).Delete
End With

Explanation

• With Set ws = ActiveSheet the active spreadsheet is assigned to the variable ws or ThisWorkbook.Sheets("Agosto") can be used to assign by Worksheet name.
• With Set tbl = ws.ListObjects("Nome_da_Tabela") the table is assigned to the variable tbl
• With tbl.DataBodyRange.Rows.Count the last line is found
• With tbl.ListRows(Número da Linha).Delete the index line n is removed

Problem in the Declaration of i

With Dim i As Integer you will have errors if the spreadsheet grows too.

Observing: Declare as Long (Dim i As Long), because many old tutorials use Integer, which has 2 bytes and the range from -32 768 to 32 767. So if the Excel version is longer than 2007, the program will stop after line 32767. Long has 4 bytes and a range of -2 147 483 648 to 2 147 486 647. Where the Excel has a limit of 1 048 576 lines.