ListObject : Enlarge the range of an existing table

[Flavien]

Hello!!

I have a listobject named "Tableau_Suivi". How can I do with VBA to include the last rows in the table? (The number of rows to be added may vary)

Sub essai2()

Dim table1 As ListObject
Dim rg As Range
Dim CL As Workbook

    Set CL = ThisWorkbook 
        
    Set table1 = ThisWorkbook.Worksheets("SUIVI").ListObjects("Tableau_Suivi")

    table1.Unlist

    Set rg = CL.Worksheets("SUIVI").Cells(2, 1).CurrentRegion
    
    ActiveSheet.ListObjects.Add(xlSrcRange, rg, , xlYes).Name = "Tableau_Suivi"
     

End Sub

Currently, I go through an "unlist" step and then I select the whole thing to rename it "Tableau_Suivi", but I think there is a more "pro" way to do it?


in advance, thank you for your help

 

[Alex Blakenburg]

Using what you already have, here is one way.
You might want to check your Table Setting in options. Typically when you add rows to the table like you have it will automatically include them in the table.

Sub essai2()

Dim table1 As ListObject
Dim rg As Range
Dim CL As Workbook

    Set CL = ThisWorkbook
        
    Set table1 = ThisWorkbook.Worksheets("SUIVI").ListObjects("Tableau_Suivi")
    table1.Resize table1.Range.CurrentRegion

End Sub

 

[Artik]

Sub essai2_1()
    Dim table1 As ListObject
    Dim CL As Workbook

    Set CL = ThisWorkbook

    Set table1 = CL.Worksheets("SUIVI").ListObjects(1)    'or ListObjects("Tableau_Suivi")
    table1.Resize table1.Range.CurrentRegion

End Sub

Artik

 

[Flavien]

Using what you already have, here is one way.
You might want to check your Table Setting in options. Typically when you add rows to the table like you have it will automatically include them in the table.

Sub essai2()

Dim table1 As ListObject
Dim rg As Range
Dim CL As Workbook

    Set CL = ThisWorkbook
       
    Set table1 = ThisWorkbook.Worksheets("SUIVI").ListObjects("Tableau_Suivi")
    table1.Resize table1.Range.CurrentRegion

End Sub

Hello Alex,

Thank you for your reply. your code works fine!

I use this type of code (below) to insert rows into my table, but as you can see (cf. picture), the line doesn't fit into the table but immediately below it, which forces me to resize it every time I add it. How do I make the row I insert part of the table?

Sub ESSAI2()

    ThisWorkbook.Worksheets("Feuil1").Range("A2:D2").Copy
    
'   Find the last line used
    DerniereLigne = ThisWorkbook.Worksheets("Feuil2").Cells(Rows.Count, 1).End(xlUp).Row
    
'   Select the following row
    ThisWorkbook.Worksheets("Feuil2").Cells(DerniereLigne, 1).Offset(1, 0).Select

    
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End Sub

 

[Alex Blakenburg]

It would be preferable to work with the actual table name but try replacing your last line code with this one.

'   Find the last line used
    DerniereLigne = ThisWorkbook.Worksheets("Feuil1").Columns("A").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

 

[Flavien]

It would be preferable to work with the actual table name but try replacing your last line code with this one.

'   Find the last line used
    DerniereLigne = ThisWorkbook.Worksheets("Feuil1").Columns("A").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Alex,

Thank you for your answer.
On the other hand, how would you do it by calling the table?

 

[Alex Blakenburg]

What is the name of the table & the heading on column 1 of the table ?

 

[Flavien]

Sheet("Feuil1") => Tableau1
Sheet("Feuil2") => Tableau13

FYI,
The size of Tablef "Tableau1" will always be the same, but the size of Table "Tableau13" will be constantly fed by the data entered in "Tableau1"

 

[Alex Blakenburg]

This is only addressing the destination table but here is one way of using table references:

Sub ESSAI2_Table()
    Dim rngDerniereLigne As Range
    Dim tbl1 As ListObject, tbl2 As ListObject
    
    Set tbl1 = ThisWorkbook.Worksheets("Feuil1").ListObjects("Tableau1")
    Set tbl2 = ThisWorkbook.Worksheets("Feuil2").ListObjects("Tableau13")
    
    ThisWorkbook.Worksheets("Feuil1").Range("A2:D2").Copy
    
'   Find the last line used based on first column in table
    Set rngDerniereLigne = tbl2.ListColumns(1).Range.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    
'   Select the following row
    rngDerniereLigne.Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End Sub