Table size change takes long time

[pro2go]

Hi there!

I am having the problem with my tables in Excel (some, but not all, containing some formulas, like vlookups, also to another workbook). Tables are of different sizes, some just few hundreds rows and few columns, largest has few thousands rows and 30 columns.
When resizing the table, for example adding column or deleting the row, it takes enormous time, sometimes over 30 minutes. My PC is quite fast, also tried on different machines, some older would just crash.

What might be the reason and how to avoid this?

 

[Yongle]

Try setting calculation mode to manual

see ... Formula tab \ Calculation Options \ Manual

Remember to reset to automatic afterwards


Or put this code behind a button and toggle between automatic and manual

Dim msg As String
With Application
Select Case .Calculation = xlCalculationManual
    Case True: .Calculation = xlCalculationAutomatic: msg = "automatic"
    Case Else: .Calculation = xlCalculationManual: msg = "manual"
End Select
End With
MsgBox "Calculation set to " & msg

 

[Fazza]

might need some further information

my first thought is that there are very slow formulas: do you have any array formulas? also, to formulas referring to whole columns - such as someforumula(input_whole_column)
second thought is there is VBA event code: do you have any VBA?

 

[pro2go]

Try setting calculation mode to manual
see ... Formula tab \ Calculation Options \ Manual
Remember to reset to automatic afterwards
Or put this code behind a button and toggle between automatic and manual

Yes, actually the workbook calculation settings are set to manual.

might need some further information
my first thought is that there are very slow formulas: do you have any array formulas? also, to formulas referring to whole columns - such as someforumula(input_whole_column)
second thought is there is VBA event code: do you have any VBA?

That is the point. Indeed, some of tables contains quite complex formulas, but some of them are nothing but simple data in few columns. Still, adding a column, row, or removing it takes ages...

 

[Yongle]

One other thing to check is the size of the used range in each sheet
Activate each sheet in turn and see where shortcut {CTRL}{END} takes you
If the cursor ends up at the end of the data, then all is well
If it ends up way over to the right or thousands of rows down the sheet, then a lot of unnecessary memory is being used and requires addressing

 

[Fazza]

simple data isn't a problem, complex (or even simple looking) formulas might be if they are inefficient/slow

consider temporarily removing the formulas one group at a time to identify any that are the issue

 

[pro2go]

One other thing to check is the size of the used range in each sheet
Activate each sheet in turn and see where shortcut {CTRL}{END} takes you
If the cursor ends up at the end of the data, then all is well
If it ends up way over to the right or thousands of rows down the sheet, then a lot of unnecessary memory is being used and requires addressing

Good point, but the end of data is more/less where the table ends.

simple data isn't a problem, complex (or even simple looking) formulas might be if they are inefficient/slow
consider temporarily removing the formulas one group at a time to identify any that are the issue

Thanks, will do that.