Removing "(blank)" from the pivot table's report filter dropdown list

murman01

New member
Joined
May 14, 2015
Messages
4
Reaction score
0
Points
0
Excel Version(s)
2016
I've created a datasheet from which a pivot table will draw from. The datasheet will have rows added or deleted monthly. In creating the pivot table, my data source has been defined as A8:AY400; however, currently only cells A8:AY210 are populated. I picked 400 rows as I don't know how to get the pivot table to only report on the rows that have data in them, knowing that the number of rows in the datasheet is dynamic each month. Column A contains the name of salespeople. Cell A4000 has "END" in it so that my VBA code knows where the last row is when macros are run. The problem is when the pivot table displays the dropdown list from the report filter, both "END" and "(blank)" are options along with the names of the salespeople. The user can pick and choose which sales people the report will be generated for, but when the user chooses the "(select all)" option from the filter list, the pivot table reports on "END" and "(blank)" too. How do I get this to stop happening? Do I need to create some sort of VBA code that hides the dropdown list generated by the filter list and then create a fake dropdown list that the pivot table will use as a filter for generating the report OR is there some sort of option within the pivot table I can set? Thanks!
 
Do you not know how to change your DATA SOURCE (PIVOTTABLE TOOLS - ANALYZE tab, CHANGE DATA SOURCE and just delete the "400" at the end of the pre-populated range and replace with "210" as in your example above) or do you want to find an alternate method to do this? Seems to me that it would be quickest if you made the change once a month and allow the users to not have to deal with the issue each time they needed a report.

Hope this helps!
cbug
 
Hey Candybg. Yes I do know how to change the DATA SOURCE and I could do it each month, but I have 31 staff that are each using the spreadsheet at their individual locations and each staff will have a different number of rows each month. Me changing the DATA SOURCE monthly would be cumbersome and not efficient; especially as the number of locations in the future that will be using the spreadsheet continues to grow. This is why I'm looking to find an alternate method. Any suggestions? Thanks.
 
Back
Top