Excel crashes when unhiding or hiding sheet

**Lifesigns** · 11-25-2010, 01:49 PM

Hi All,

Been using Excel for a few years now but VBA for a short while so I'm not sure how everything fits together. I've noticed something with two Workbooks I've created which is rather annoying.

They both have Pivottables that have their Datasources on seperate sheets that are usually hidden. As 2007 doesn't have the slicers 2010 has I use a dropdown so the user can select the required date and then this is applied to the Pivot as a filter. The problem I have is when hiding/unhiding the data sheets Excel freezes up. I can still get in to the VB editor with Alt+F11 and save or check the code but Excel is frozen. If I kill it with Task Manager and re-open the file then it is saved with the sheet now visible but when I hide it I have the same issue again.

I would appreciate any views on why this could be happening.

**Richard Buttrey** · 11-25-2010, 01:53 PM

Hi,

Not sure why it's happening but I've noticed similar behaviour with stuff not necessarily related to pivot tables.

My quick and dirty solution is to use the Immediate window in the VBE and type Range("A1").Select. This clears the frozen window. You could allocate this to a small one line macro in your Personal workbook if you prefer.

I know it's not a solution but it would avoid using Task Manager and perhaps losing data.

Rgds

**MarvinP** · 11-26-2010, 12:04 AM

Hi Lifesigns,

I've had a problem with pivot filters using VBA. You can not remove all the filters with vba or you get an error. I've had to shift what is checked in the pivot filter to insure at least one is checked before it will work correctly.

Is it possible that is your problem?

**Lifesigns** · 11-26-2010, 05:07 PM

Hi there,

Thanks for the help the Immediate Window did the trick.

I've noticed the issue with the filters before, was wondering if there is way to rebuild the pivotcache to remove invalid entries. Anyway I'll leave that for another day.