Long delay in calculation

[ATLGator]

Hey everyone,

Just a general question here:

In a macro I created that adds formatting and analysis to a system-generated report, I move a column of data from Column 21 to Column 1 using:

Please Login or Register  to view this content.

However, this piece seems to take up about 90% of the time that the macro takes to run. Does anyone know why this action takes so long to execute? Is there something I can do to speed this up, or replace it with something that would run faster?

Thanks!

[arlu1201]

Do you have these 2 statements in your code - 1st one at the top (after the dim statements) and the 2nd at the end (before the end sub) ?

Application.Calculation=xlCalculationManual

Application.Calculation=xlCalculationAutomatic

[ATLGator]

No, I've never used these. What do they do?

[ATLGator]

I added the two lines of code, but it took the same amount of time.

[ATLGator]

Is this maybe due to the memory involved in moving the entire column? I would really love to find a way to make this part of my macro faster, if possible.

[jaslake]

Hi ATLGator

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" worksheet that demonstrates what you wish the output to be.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the "Add Files"... button to locate your file for uploading.
6. This will open a new window File Upload...Click "Select Files"
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the "Upload Files" button and wait until the file has uploaded.
10. Click the "Done" Button.

[ATLGator]

@jaslake - attached is a sample file with the macro. However, while putting this together, I found that the macro works very quickly here, which is another weird issue.

Currently, the macro I'm working with is saved to my Personal.xlsb file and is being applied to a file that is pulled from an online database.

When pulling the file from the database, it seems to save as a .xls file but opening the saved file produces the error that the file is a different format than specified by the file extension. When I choose to open the file and then Save As, the default extension that comes up is .htm/.html. Then, even after saving the unformated file and changing the extension to .xlsx, it takes at least 10 seconds before anything from the macro executes (running from the personal file) - which is nothing like how it acts in the file I posted.

Hopefully you've had experience with this or know something about it because I don't know how to re-create the issue.

[jaslake]

Hi ATLGator

Whatever your Code is doing it does it rather fast...creates an additional 6 worksheets without error. I see nowhere in your Code any indication of this

When pulling the file from the database, it seems to save as a .xls file

There is no save line in the Code I see.