Delete table lines through VBA

Question

I’m having a hard time deleting lines from a table, not just the content, but that doesn’t affect the worksheet lines. I recorded a macro that resulted in the following code:

Range("Tabela193").Select  
Selection.ListObject.ListRows(1).Delete  
Selection.ListObject.ListRows(1).Delete  
Selection.ListObject.ListRows(1).Delete 

This worked but as the number of lines is variable, I have to put a routine that counts the number of lines and repeat the line "Selection.ListObject.Listrows(1). Delete" to the last line. Another detail is that if you have a single line and it has no data, ignore the "Selection.ListObject.Listrows(1) routine. Delete" and select another table to copy the data from it.

Answer 1

The question was somewhat generic, but let’s go to the path of stones for you to make your adaptations.

You have not explained if the intention is to delete all the table rows or just a few. From the text of the question, it seems that it is to exclude all, but as its code references a range with a specific name ("Table 193"), let’s look at both cases.

If you want to delete all lines in use in the spreadsheet, you need to reference the entire range in use in a spreadsheet. For this, we use the property UsedRange from the worksheet -- then just iterate all the lines, excluding them. For example:

Sub eitcha()
    Dim lnCont As Long

    For lnCont = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
        ActiveSheet.UsedRange.Rows(lnCont).Delete
    Next lnCont
End Sub

Remembering that activesheet may be replaced by the reference to the worksheet to be deleted (for example, in your case, Range("Tabela193").worksheet, that should lead to the same activesheet).

If you want to delete only the lines from that crease, just a small adaptation: instead of excluding all lines from the UsedRange, We will delete the Range lines("Table 193").

Sub BatataDoce()
    Dim lnCont As Long

    For lnCont = Range("Tabela193").Rows.Count To 1 Step -1
        Range("Tabela193").Rows(lnCont).Delete
    Next lnCont
End Sub

Answer 2

This way is more practical

Planilha.ListObjects("Tabela193").DataBodyRange.Delete