Hiding Columns and Rows Extreme Slow, Tip?

[Excelnoub]

I have been looking so an answer on the web but no luck. I have 5 sheets but restrictions from row 1 to 4 (They are locked and not assessable to users) but also my codes make it that no entry beyond row 20 is permitted.

So row 24 until end of excel rows... 1048576 are not even needed. When I go to Hide all unwanted rows my excel process become slow and “glitchy”

I have the following for my procedures:

Application.ScreenUpdating = False / True
Application.EnableEvents = False / True

But nothing seems to be fixing this lag.

Is there any tricks, as soon as I unhide all column or rows then it goes back to working again and fixing the lag issues.

Weird cause I have no formulas and code working in these section pass row 24... (Well 20 in coding but I have command buttons in 23 disabling me to hide 21 to 23)


Any thoughts?

[Winon]

Hello Excelnoub,

It would be good if you could post a sample Workbook with your Code for us to have a look at for you.

Regards.

[Richard Buttrey]

It's almost certainly because you are making use of some volatile actions, no doubt hiding or filtering rows, and if you are using some volatile functions then you will be compounding the problem.

See this Microsoft discussion topic
http://msdn.microsoft.com/en-us/libr...ffice.14).aspx

Page down to the section which starts with
"Speeding up Calculations and Reducing Obstructions"
and see if that offers any help.

[Winon]

@ Richard Buttrey,

Hello Richard,

I think you are spot on! That is why I have asked to see a sample Workbook with Code and all.

Kind Regards.

[Excelnoub]

Here is an example of my Workbook

WLRTestdata.xlsm

Just by going from tabs it slows down and/but when you will get or switch from Tab "Contracts" and "Report", that doesn't have hidden rows/column, it doesn't have to lag'ish feel.

[Richard Buttrey]

Hi,

I don't understand why you think it 'slows down' since it seems fairly normal to me but thereagain I don't really understand your whole process.

Is this merely an example of a much larger workbook which does have lots of data since this has none.

[Excelnoub]

Actually everyone will have a blank workbook like this one and start from scratch....

I have tried something else, Instead of hiding everything, I have (I was in Break Page view) selected normal view and unselected "Gridlines" and it seems to get the workbook to run faster...

All of my other cells (Column O to end and row 21 to end) are locked... would this be an issue?