VBA: To dynamically insert (n) number of rows in a table

[inddon]

Hello There,

I would like to know how to insert (n) number of rows in a given table using VBA.

There are 3 worksheets. Each worksheet has one table. In some of the columns it has formulas.

Sheet1 has Table1
Sheet2 has Table2
Sheet3 has Table3

Old data/rows from the tables are deleted. This new process will insert new rows based on the below variable:
NumberofRowstoInsert as Integer 'Eg. This gets a value 10

The control should go on the first row of the table and insert 10 rows below. It should do the same for the other tables as well.

Could you please advise, how this can be done?

Thanks & regards,
Don

 

[inddon]

Hello There,

Table1 has only 1 row.

I came up with the below code, which works fine, except it creates rows before the first row. It should create rows after the first row. Any ideas how this can be done?

Dim cnt as long

cnt = 10
ActiveSheet.ListObjects("Table1").DataBodyRange.EntireRow.Resize(cnt).Insert

Thanks & regards,
Don

 

[Marc L]

Hi !

Maybe try with ListRows property …
​

 

[inddon]

Hi !

Maybe try with ListRows property …
​

Hi Marc,

I tried with this one, but it inserts only one row at a time.

ActiveSheet.ListObjects("Table7").ListRows.Add AlwaysInsert:=True

Based on a variable, it should bulk insert rows at the end of the table, and not to loop through.

For deleting bulk rows in a table, I am using the below code. Can something like this be used for creating rows as well?:

    'Delete all table rows except first row from table1
    With tbl1.DataBodyRange
         If .Rows.Count > 1 Then
              .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
         End If
    End With

Could you please advise the exact code.

Thanks & regards,
Don

 

[Comfi]

I do know I am responding to an old thread, but the issue of the topic starter did put me on the trail to the solution to circumvent the 1 row at a time limitation of the ListRows command (and it's extremely poor performance) when I ran into this situation myself.

What I did is the following:
You start with your one row table,
Then you add just ONE row using the ListRows command. (which is much more acceptable performance wise)
After that you can use the range insert command to insert the remaining rows inbetween the original row that the table had and the one row you just added. That way you have effectively added all the desired rows below the original row in a pretty efficient way.

Thanks for putting me on the trail for this solution @inddon