Running macro after data retrieve is slow

amalsp

New Member
Joined
Aug 31, 2018
Messages
5
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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
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?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top