Speed up Add Rows VBA

[Chewyhairball]

Hi

I have this bit of code that adds a specified number of rows to table 1 depending on the value in A15.

It works fine but is a bit slow when adding a large number of rows. It is triggered from a button on the sheet.

I was wondering if there is a more efficient way of writing this to make it faster?

thanks

Rory

 

[Chewyhairball]

Hi

I have this bit of code that adds a specified number of rows to table 1 depending on the value in A15.

It works fine but is a bit slow when adding a large number of rows. It is triggered from a button on the sheet.

I was wondering if there is a more efficient way of writing this to make it faster?

thanks

Rory

should probably add the code

Dim i As Integer
For i = 1 To Range("A15").Value
ActiveSheet.ListObjects("Table1").ListRows.Add
Next i

 

[Peter_SSs]

See if this is any better.

With ActiveSheet.ListObjects("Table1")
  .Resize Range(.Range.Resize(.Range.Rows.Count + Range("A15").Value).Address)
End With

 

[Chewyhairball]

See if this is any better.

With ActiveSheet.ListObjects("Table1")
  .Resize Range(.Range.Resize(.Range.Rows.Count + Range("A15").Value).Address)
End With

Hi
I am getting some kind of runtime overlap error. Possibly due to there being data underneath the table.

 

[Peter_SSs]

I am getting some kind of runtime overlap error.

That is pretty vague. What is the exact error?

Possibly due to there being data underneath the table.

Can you give details if what it is underneath the table and where it is? Unless we can reproduce what you have it is hard to move forward.

 

[Chewyhairball]

Hi

This is the error i am getting

Underneath each of the table columns there are columns of typed data, formulas and merged cells.

thanks

Rory

 

[Peter_SSs]

Underneath each of the table columns there are columns of typed data, formulas and merged cells.

So what do your expect to happen to that "typed data, formulas or merged cells" when rows are added to the table above them?

 

[Chewyhairball]

So what do your expect to happen to that "typed data, formulas or merged cells" when rows are added to the table above them?

To move down as new rows are added. Which it does with my current code but it gets slow when dealing with 10 or more rows.

 

[Peter_SSs]

Underneath each of the table columns there are columns of typed data, formulas and merged cells.

To move down as new rows are added. Which it does with my current code but it gets slow when dealing with 10 or more rows.

For me it depends exactly what is below and exactly where it is below but your existing code also errors for me is certain circumstances too.
So I guess my test set-up is not identical to yours - which makes it hard to offer suggestions for improvement.
If you would like it investigated further then perhaps you could provide a sample file (no sensitive data) via a shared link posted here to a file uploaded to DrpBox, OneDrive, Google Drive etc

 

[Chewyhairball]

Hi Peter

I think I will just leave it as it is for just now and maybe come back to it at a later point when I have more time.

Thank you for your help.

Rory