Removing Blank rows in Pivot Tables

T

Tom

Hi guys.

I have created a pivot table, from a range of data which
looks like this:

Name Subject Subject Subject
a result result
b result
c result result result
d result result

Hopefully that comes out OK. You can see not everyone has
a result for each subject. I want to list each result for
each subject they take ONLY. At present, the table
supplies each subject, with a value of nil. I just want
it to display those they have results for!

Hope you can help,

Tom.

PS The actuall data will be about 300 students with
around 30 potential results.
 
D

Debra Dalgleish

You could use a macro to hide the worksheet rows where the pivot table
row is zero total. Then, use another macro to unhide all the rows when
required:

'===================
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
'=======================
 
Top