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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
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

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

Watch MrExcel Video

Forum statistics

Threads
1,129,479
Messages
5,636,575
Members
416,925
Latest member
malamutus

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
Top