Columns take forever to unhide

Originally Posted by TMShucks

If you have a button, you must have code. So, you've not (yet) shared the workbook with us and you've not shared the code.

I tried to delete a bunch of vlookup formulas in the Table, no difference.
I also tried to hide and unhide manually, takes about the same time, 17 seconds for 5 columns.

Originally Posted by TMShucks

How and where would you want us start to diagnose the problem?

Well, I don't really. That's why I started by asking if someone had experienced something similar. I am glad someone responded.

Originally Posted by TMShucks

I don't really understand why "force recalc with Ctrl + Alt + F9" hides or unhides columns.

It doesn't. That was just to prove that my calculations are not that bad. If they are triggered by unhiding, I'm not sure...

Originally Posted by TMShucks

Why do you hide columns in blocks of 5? 30 seconds to hide columns seems a lot, let alone 150 seconds to unhide them.

Two columns of data that user fills in, then 5 columns with vlookup formulas, then two columns of data that user fills in, then 5 columns etc. etc.

Originally Posted by TMShucks

Why is the workbook 1.65 Mb? 1000 rows by 100 columns doesn't sound that big.

Beats me. Workbook contains 10 sheets in total with various amounts of data on them but nothing nearly as big as the table. I attack any false usedrange like a tiger. Maybe not like a tiger but it is a pet peeve of mine. There is a "delete all" button that resets all used ranges. Then I used this Tables or dynamic ranges to avoid dragging formulas down over hundreds of extra empty rows.

Originally Posted by TMShucks

Do you stop screen updating when you hide the columns? Do you switch off calculation?

Yup. Screenupdating is off. Calculation I tried both on and off, doesn't make much difference.


Just now I tried two things. I converted the Table to a range, then tried to hide, unhide some columns. It was instant.
I then tried and copy the whole range and paste it on a new worksheet. It remained a table, Excel just gave it a new name. I then tried hide, unhide and it was instant.

The reason why I don't just happily carry on from here is that I have 111 named ranges on that sheet and they don't copy. Quite a few of them are columns, maybe that's where the problem is...

I now deleted all the other worksheets, all the modules (15 or so), all the named ranges and all the formulas, all the buttons, all the rows above the table. And I saved a copy of that.
When I deleted the last formula

Formula:

=IF([@[Compartment  code]]="";[@[Room/ area]];[@[Room/ area]]&" "&[@[Compartment  code]])

it seems there was an improvement. I estimate it to be twice as fast now, only 75 seconds of waiting for unhiding some columns, yeah!