Pivot Table Scroll Area Issues

**bennon05** · 08-07-2012, 09:44 AM

I have a Pivot Table whose source data is an external access database. I have created a macro that filters the Pivot Table based on a cell value (Excel 2010). Everything functions as it should other than the Scroll Area. The access database has over 1 million rows, but most of the time, after filtering, there are <10,000 rows with data. However, for some reason, excel is still showing the Used Range as the 1 million rows and is causing the actual scroll bar to be very small and difficult to use. I know I can reset the Scroll Area which i've done, but that only limits how far down you can scroll and doesn't effect the actual scroll bar shown on the screen.

When I've tried to reconfigure the UsedRange property of the pivot table, i've received an error saying that I cannot more or alter a pivot table. Here's the code i've used:

Please Login or Register  to view this content.

My question is: Is there anyway I can adjust the UsedRange of the Pivot Table so that the scroll bar will accurately display?

Thanks in advance.

**Pichingualas** · 08-07-2012, 10:21 AM

Try pressing Ctrl+End, highlighting the row below that and pressing Ctrl+Shift+Down, then right click anywhere in the selection and choose delete.

**bennon05** · 08-07-2012, 11:46 AM

That didn't work. It still brings up the 1 million cells even after delete. I'm quessing that the issues are due to the Pivot Table.

**Pichingualas** · 08-07-2012, 12:12 PM

Try making it again on another worksheet or closing and reopening the file. Every time I've seen this error it was due to some cell being formatted outside of the range you are using, so excel would take it into account when making the scrollbars adapt.