VBA - Delete only last row of the table that contains a text

Question

VBA - Delete only last row of the table that contains a text

Navigation

#1 by (0 votes)

0

I have a somewhat specific question. I hope you can help me

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

Whenyouclickthe+buttonfor"Answered Calls", it adds a line in the "Activities" table with the text "Phone Answer".

I would like to be able to do the same thing with the minus button, when clicking on it, the last line containing "Telephone Answer" is deleted (only the last one inside the table), but not interfering with 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 do not know how to identify only the last value containing the specific text for each minus button I add.

excel vba planilhas

asked by anonymous 05.09.2018 / 00:10

1 answer

How to make php send commands to an Elgin l42 in BPLA language? How to create an object through a String? [duplicate]

score 0 · Answer 1

Since the ListObject is being used, code to delete the last line.

Last row of table with value

This is a code that creates an array with the values of the table and makes a reverse loop (from the last row to the first) in column 2 by looking for the value of "Answered Calls" and if it finds, removes the row and exits 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 worksheet is assigned to the variable ws or ThisWorkbook.Sheets("Agosto") can be used to assign by the name of the Worksheet.
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 Declaration of i

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

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