Not display rows where grand total is 0
Suggestion:
make sure that you have on the left side of the pivot table at least
two columns space, say A and B. In B you calculate the grand totals
(e.g =sum(C6:s6)). In A you enter the formula
=if(B6=0;"hide";0)
Then you write a pice of code:
sub HideZeroTotal()
Dim rngArea As Range
Application.ScreenUpdating = False
ActiveSheet.Range("a6:a300").Select
Selection.EntireRow.Hidden = False
ActiveSheet.Calculate
Selection.SpecialCells(xlCellTypeFormulas, xlTextValues).Select
For Each rngArea In Selection
rngArea.EntireRow.Hidden = True
Next
ActiveSheet.Range("n2").Select
Application.ScreenUpdating = True
end sub
Here it is assumed, that the area of interest is between rows 6 and
300. Whenever you run the code, the lines where grand total = 0 will
be hidden.
Good luck
Udo
wacNTN schrieb:
I have pivottables that have rows where the grand total is 0. I want to
hide these rows automatically but I can't figure out how to filter them out.
I would even settle for sorting on grand total but that one alludes me too.
Any help will be appreciated!
Walter
--
WAC
|