0

I have a large Excel workbook that imports data from some local SQL servers via Power Query and performs a bunch of calculations on the imported data in an Excel Table. Because the table containing calculations is large the workbook size is almost 100mb, with the imported data being only 6-7mb.

Strangely, I have discovered that after refreshing the links to external data and saving the file, a macro to simply hide/unhide columns in the calculations table is very slow every time and takes 2.5 mins simply to unhide columns! However, when I save, close and reopen the file and run the same macro the hide/unhide is instantaneous every time.

Below is a copy of the (very simple) macro to unhide the columns:

Application.ScreenUpdating = False
Application.Calculation = xlManual
    
    Columns("M:LH").Hidden = False

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

I would obviously prefer not to be constantly closing and reopening the workbook. Does anyone know why the macro might be performing so slowly after the source data is refreshed and how I can make it work instantaneously without having to close/reopen every time ?

Any insight anyone can give is appreciated.

Improve this question
9
  • After refreshing the source table and performing the initial calculations, do the calculations need to remain "live", or could you replace them with the cells' values? Commented Nov 9, 2024 at 0:28
  • Application.Calculation = xlAutomatic is enforcing your sheet to recalculate again. Is not a matter with your query, but the activation of the automatic calculation mode. It is acting as pressing "F9" to recalculate. Commented Nov 9, 2024 at 0:58
  • I don't think that Application.Calculation = xlAutomatic neccessarily triggers a recalculation Commented Nov 9, 2024 at 2:35
  • Tim Williams, unfortunately the calculations need to remain live because users of the file can input values into a small number of columns to to scenario modelling. It would be a lot easier if I could simply turn them all with values. Commented Nov 9, 2024 at 2:45
  • I should emphasise, the same macro runs both before and after reopening the workbook, but is drastically slower before reopening. I.e. I don't believe the delay is due to the calculation method. Commented Nov 9, 2024 at 2:47
Related questions
0
Hide/Unhide Excel Sheets based on multiple cell values
1
Refresh entire Excel workbook (all data connections and calculations) every 15 minutes?
0
Trying to create a VBA macro in excel to hide/unhide specific rows
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.