Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Not display rows where grand total is 0
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automate display/ hide rows in excel based on yes/no check box | Excel Discussion (Misc queries) | |||
Need 2 rows to display X axis data points for a line graph | Charts and Charting in Excel | |||
formula to use when number of rows changes dynamically | Excel Worksheet Functions | |||
Hiding Rows if the linked rows are blank | Excel Discussion (Misc queries) | |||
Display selected rows from one worksheet to another | Excel Worksheet Functions |