VBA Code to Add Rows is slowing down

[NoiCe]

Hello,

For some reason, all of a sudden, the following code is slowing down when I see the additional rows being added one by one, whereas in the past, I would have the circle spin for a few seconds, and all added rows would appear. I could use some expertise in optimizing the code as I took bits and pieces from different sources as I'm not great at VBA. TIA

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[6StringJazzer]

For starters. I suggest managing the screen updating outside the loop.

Please Login or Register  to view this content.

Since I can't run this in a blank file I also request that you attach the Excel file with some sample data so we do a dynamic analysis of the code.

[NoiCe]

Hi I know it's been a little while, but here is an example of the worksheet. As you'll see, when you click on the Add Row button, it goes through each cell to enter the information in each row. In my actual workbook, I have over 1000 rows and now it's starting to get very slow. Trying the best way to add "x" rows after the next available rows.

Example.xlsm

Thx for your help!

[jolivanes]

Have not looked at your attachment but another change needed is the selecting.
Selecting slows down macros tremendously.
Use With ....... End With instead or just Range(whatever range here).Formula = "= Blah Blah"

[jolivanes]

In the "AddEntry" macro, you have "Dim i" which makes it a Variant. Should be "Dim i As Long" or "Dim i As Integer"
See here:
https://learn.microsoft.com/en-us/pr...ectedfrom=MSDN

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Just a couple things I noticed when glancing at your code.

[6StringJazzer]

Your button calls a macro in another file

'C:\Users\jbala\Desktop\TEST\Balaban_Finance.xlsm'!AddEntry

which of course we don't have.

I'll just assume it's OK to run the code directly by calling the sub instead of from a worksheet button

[6StringJazzer]

rowData does everything by selecting a range then acting on Selection. This is relatively slow, and is probably a result of using the macro recorder. The code should be changed to directly operate on the relevant ranges, but it will take a while to do the analysis and make the changes.

What would really help is an English description of what you want to your code to do, so I don't have to spend as much time reverse engineering it.

[NoiCe]

Hi,

I want to add rows to my sheet that include the formatting and formulas that I have in Module 2. They should be added after the last row that has data in it (determined by Column A).

Thanks again!

[Artik]

I think the main reason for the slowdown is the large number of CheckBox controls. Many times I noticed slowing down with a large number of graphic objects in the sheet (controls are also graphic objects).

Artik

[NoiCe]

What would you recommend instead of the checkbox?

[6StringJazzer]

You can use Worksheet_SelectionChange and put X's directly into the cell (or remove) when selected. See updated file. Also needed change to column J.

To amplify earlier comments, you are using Select and then acting on Selection or ActiveCell. This consumes a lot of resources. It is better to directly address the cell than to Select it and then act on ActiveCell.