Running macro after data retrieve is slow

[amalsp]

Hi All,

I have a MS SQL stored procedure returning data to excel. After retrieval I carry out some actions using a macro. When I run the query and then run the macro, the macro takes a long time. If I run the query save, close and reopen the workbook and run the macro first, then it executes fast. I am using O365 excel and SQL standard 2014 on windows 10. Query has about 35000 records and about 150 columns.

I have the usual calculation to manual and screen update set to false before running the macro.

Any idea where to look for the issue?

Thanks in advance.

Amal

 

[Yongle]

Does the used range require resetting? (it is reset automatically when the workbook is closed & reopened)

After retrieval ...
- use shortcut {CTRL} {END} to take you to the last used cell in the worksheet
- is the found cell at the end of your data or in some other cell many rows further down?

 

[amalsp]

CTRL + END takes me to the last used cell.

The issue is that I am running through all the rows and making zero some values base on a condition check. Even from the first row, the macro runs very slow. Like one row per second. If I run the macro before running the data query, the 3500 rows are covered in about 75 seconds.

If I debug and check the calculation options, it shows as manual as I changed it in the macro. Could it be that even though it shows as manual it is on Automatic?

Does the used range require resetting? (it is reset automatically when the workbook is closed & reopened)

After retrieval ...
- use shortcut {CTRL} {END} to take you to the last used cell in the worksheet
- is the found cell at the end of your data or in some other cell many rows further down?