PDA

View Full Version : Pivot Table Macro to lock manual filter/remove blank rows from datasheet



stillserene
06-02-2009, 05:14 PM
Hi,

I have an excel model in which my pivot table is linked to a datasheet, and the datasheet is set to dynamically re-size anytime new data is pasted into a raw data tab. The pivot table also automatically updates whenver new data is pasted into the model. However, my pivot table keeps showing errors because it is picking up the blank rows in my datasheet. These pivot table errors go away when I set a manual pivot table filter to not include blank cells. How can I get rid of the blank cells in the pivot table? My thoughts were to:

(1) write a macro to limit the data range of the pivot table to filter-limited data in the datasheet

(2) write a macro to lock the manual pivot table filter

Any ideas? thanks!!