calculated field/item in pivot tables

[Guest]

I have a pivot table that summarizes instructor rank by gender for each
Faculty and department.
Prof Assoc Prof Assist Prof
Instructor Lecturer Total Faculty Dept M F M F
M F M F M F

Arts Arch
Arts Psyc
Sci Math
Sci Phys

The pivot table will show a grand total for each row (ie for each
department). However, it is a single number and not broken down by gender.
When I click on 'rank' and try to add a calculated item called 'Grand Total'
with the formula: Professor + 'Associate Professor' + 'Assistant Professor' +
Instructor + Lecturer, I do get a new column called 'Grand Total' with a male
and female breakdown. But now each Faculty shows all possible departments
instead of just the departments that belong in that Faculty. For example,
Math shows up in all Faculties but should just show up under Faculty of
Science. The data are blank for all Math rows except for the one in Science.
How do I get rid of all the blank Math rows except for the one in the
Faculty of Science?
Thanks,
Liny.

 

[Debra Dalgleish]

You can use programming to hide the rows with a zero total. For example:

'======================================
Sub HidePivotZeroRows()
'hide worksheet rows that contain all zeros
Dim rng As Range
For Each rng In ActiveSheet _
.PivotTables(1).DataBodyRange.Rows
If Application.Sum(rng) = 0 Then
rng.EntireRow.Hidden = True
Else
'unhide any previously hidden rows
rng.EntireRow.Hidden = False
End If
Next rng
End Sub
'================================
Sub UnhidePivotRows()
'unhide all rows
Dim rng As Range
For Each rng In ActiveSheet _
.PivotTables(1).DataBodyRange.Rows
rng.EntireRow.Hidden = False
Next rng
End Sub
'====================================