Columns take forever to unhide

I have a Table that is about 1000 rows and about 100 columns. I have a checkbox with which I can hide and unhide 50 columns divided in groups of 5.
Hiding them takes about 30 seconds. Unhiding them takes about 150 seconds or 2 min 30 seconds.

For comparison, if I force recalc with Ctrl + Alt + F9 it takes about 4 seconds.

The weird thing is that it wasn't as slow before. Sometime during the last two days I did something that made it slow.

Sorry for not posting the workbook right now, it would take awhile to prepare. It's about 1,65MB the way it is.

Has anyone experienced something similar? Or even better, has anyone a solution?

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.

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

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

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

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

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


Regards, TMS

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!

And now I also tested to delete all 111 variables and that made no difference.
I think that Table is corrupt somehow...

The only thing I can think of is that you have formulas in whole columns and there's a lot of calculation going on when Excel is redrawing the screen.

But, purely a guess with nothing to look at

Believe it or not, your feedback put some pressure on me to do things.
My last test was to just copy this single sheet that I had left. And that seem to do the trick AND it let me keep my named ranges. My day has ended now and first thing in the morning I'll try that. I might even post something.

Testing, testing and more testing. I have a v.27 that takes 1.5 seconds and a v.28 that takes 2.5 minutes to unhide columns. I started from rev 27 and started adding stuff to make it into rev 28, step by step. Then I discovered that it was the excellent formula that tigeravatar provided here: http://www.excelforum.com/excel-form...ile-names.html that was the culprit. I've tested it three times now and it's beyond doubt that it's when I add that formula that it happens. Starting on a newly opened v.27 each time. Formula looks like this:

Formula:

=IF([@[Tag no]]="";"No tag no!";

  IF(SUMPRODUCT(LEN([@[Tag no]])-LEN(SUBSTITUTE([@[Tag no]];MID("<>:""/\|?*";ROW($1:$9);1);"")));"Illegal Tag no!";

  IF(OR([@[Longitudal distance

(from FWD port corner of room)]]="";[@[Transverse distance

(from FWD port corner of room)]]="");"No item coord.!";

  IF(OR([@[Longitudal 1]]="";[@[Transverse 1]]="");"No rigging coord";""))))

Adding the same formula without tigeravatars line is no problem what so ever.

However, the weird part is that once I added it, the table is forever contaminated. Deleting the formula or replacing it with something else has no effect, it remains slow. I even deleted the formula column, no difference.

I made a mock up table and tried to recreate the error but no. I guess it's just a matter of if you provoke Excel enough, move things between sheets and workbooks back and forth, move columns around and you have a pretty complex setup to start with, bad things WILL happen.
I have no time to dig any deeper into this. I consider this solved.
Thanks to TMShucks for kicking me into action!

You're welcome. Thanks for the rep.