Delete table rows through VBA

Question

Delete table rows through VBA

Navigation

#1 by (0 votes)

0

I'm having a hard time deleting rows from a table, not just the content, but this does not affect the rows in the worksheet. I saved 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 since the number of rows is variable, I have to put a routine that counts the number of rows and repeat the line "Selection.ListObject.ListRows (1) .Delete" until the last line. Another detail is that if you have a single row and have no data, ignore the "Selection.ListObject.ListRows (1) .Delete" routine and select another table to copy the data from it.

excel-vba

asked by anonymous 30.04.2018 / 18:42

1 answer

Hibernate does not connect to MySql database Recover login when registering

score 0 · Answer 1

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

You have not explained whether the intention is to delete all table rows or just a few. By the text of the question, it seems that it is to delete all, but as its code refers to a range with a specific name ("Table193"), let's look at the two cases.

If you want to delete all rows in use in the worksheet, you need to reference the entire range in use on a worksheet. For this, we use the UsedRange property of the spreadsheet - then just iterate all the rows, 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 can be overridden by the reference to the worksheet that should be deleted (for example, Range("Tabela193").worksheet , which should result in the same as activesheet ).

If you just want to exclude the rows from that range , a small adaptation is enough: instead of deleting all rows from UsedRange , "Table193").

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