Excel – What can slow down Excel in copying worksheets, but doesn’t slow down general responsiveness

microsoft excelmicrosoft-excel-2010

I've got a fairly complex workbook that I'm building, and it looks like it's going to need a number of the sheets copied 10-20 times over – where each copy operation takes ~45s at the moment (over around 20 sheets). This seems unreasonable so I'm trying to trace what could be the culprit. So far I have:

  • Disabled macros and addins prior to copying –> no effect
  • Deleted all named ranges (the workbook contains a LOT of these, and each worksheet contains quite a number as well, many of which are dynamic, using the OFFSET(A1,,HEIGHT,WIDTH) approach) –> No effect
  • Deleted all but a couple of sheets prior to copying –> no effect
  • Ensured no formulae refer to very distant ranges –> no effect

In each case, I'm setting up the condition, closing the workbook and re-opening it prior to attempting a copy, and I should also mention that calculations are disabled (and in any event, despite a considerable number of volatile formulae, calculation takes still no more than around 5s, which should not result in a 45s copy time). Excel has plenty of memory available, so this SHOULDN'T be the issue, and I've tested on a machine with an SSD with the same results, which implies it's not a swap issue. Any other ideas?

Best Answer

Find the last row, select down (Shift-down) and delete rows. Same for columns. It could be that someone applied a formatting on 1M lines.

Related Question