Adding rows to a tables existing range with VBA

Question

I'm trying to extend the existing range of all my tables by a 100 rows, when a button is pushed.

I came up with some simple code, but it's really slow and for some reason the isn't filling one of the rows with the formula the way it should.

I'm new at coding, so i'll be gratefull for any advice. Edit: I've added a picture of the formula after i run the code.

Sub ExtendRows()
Dim i As Long, j As Long, ws As Worksheet, oListRow As ListRow

Set ws = ActiveWorkbook.Worksheets("Holdbarhed")

Application.ScreenUpdating = False

For i = 1 To 100
    For j = 1 To 10
        Set oListRow = ws.ListObjects(j).ListRows.Add
    Next j
Next i

Application.ScreenUpdating = True
End Sub

Picture of formula not updating correctly:

You probably want calculation off while processing your code. Should speed up the proces, Application.Calculation = xlManual ..... Your code ..... Application.Calculation = xlAutomatic — JvdV
– JvdV, Commented Feb 21, 2019 at 8:48
It helped a little on the proces time! I still takes like 20 sec. to run the code. — Johan Larsen
– Johan Larsen, Commented Feb 21, 2019 at 8:57
Note that the automatic filling of the formulas only works if the formula is the same in the whole column — Pᴇʜ
– Pᴇʜ, Commented Feb 21, 2019 at 8:58
I have just added a picture of the column and it should be the same. — Johan Larsen
– Johan Larsen, Commented Feb 21, 2019 at 9:04
@JohanLarsen do you see the little green arrows on the left corner of the last 3 rows in the picture? They probably tell you that this formula is not the same as above (hold your mouse over them)! There is a jump from N100 to N201 that means the formulas are not the same as in the row above. If they were the same they should go from N100 to N101. Because they are actually not the same Excel cannot autofill them. — Pᴇʜ
– Pᴇʜ, Commented Feb 21, 2019 at 9:08

Pᴇʜ · Accepted Answer · 2019-02-21 16:01:21Z

1

The issue with the speed is that looping and adding a row each time is indeed very slow and adding 1000 rows actually takes around 20 seconds!

Each interaction (add row) with the worksheet takes its time. But it takes almost the same time whether you add 1 row at a time or 100 rows at a time. So adding each row in a distinct command takes 100 times longer than adding 100 rows in one command.

Now there is the problem that list object tables has no command to add mutliple rows at once. But you can reduce the amount of interactions using a workaround for this:

Add 100 rows at once, below each list object table (99 distinct adding actions less than before).
Then resize the table to that new space.

This reduced time in my test to 0.8 seconds (10 tables adding each 100 rows). Of course this workaround only works to add rows in the end of the list object table.

Public Sub ExtendRowsSpeedyGonzales()
    Const ROWS_TO_ADD As Long = 100  'amount of rows to add each table
    Const TABLE_COUNT As Long = 10   'amount of tables

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Holdbarhed")

    Dim iTable As Long
    For iTable = 1 To TABLE_COUNT
        With ws.ListObjects(iTable)
            Dim OldTableRange As Range
            Set OldTableRange = .Range 'remember original size of table

            'add rows BELOW table
            .Range.Offset(RowOffset:=.Range.Rows.Count).Resize(RowSize:=ROWS_TO_ADD).Insert Shift:=xlDown

            'resize table
            .Resize OldTableRange.Resize(RowSize:=.Range.Rows.Count + ROWS_TO_ADD)
        End With
    Next iTable

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

edited Feb 21, 2019 at 16:01

answered Feb 21, 2019 at 10:02

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Johan Larsen Over a year ago

It's perfect! I still got a column that isn't filling out as it should, but it can just be done manually.

Pᴇʜ Over a year ago

@JohanLarsen You could try to construct a minimal reproducible example of the formula issue and ask it in a new question. If you are still interested in getting this solved.

Error 1004 · Accepted Answer · 2019-02-21 09:22:46Z

0

I use this piece of code which seems to copy also formulas:

Option Explicit

Sub test()

    Dim tbl  As ListObject, i As Long

    With ThisWorkbook.Worksheets("Sheet1")

        Set tbl = .ListObjects("tblTest")

            For i = 1 To 3
                tbl.ListRows.Add
            Next i

    End With

End Sub

Results:

answered Feb 21, 2019 at 9:22

Error 1004

8,2883 gold badges36 silver badges58 bronze badges

1 Comment

Johan Larsen Over a year ago

There isn't a big difference on my code and this code. When i have to add a 100 rows to 10 columns still takes like 20 sec and it still wont autofill the formula correctly. It's almost quicker to manually resize the columns :-(

Collectives™ on Stack Overflow

Adding rows to a tables existing range with VBA

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related