Removing Blank rows in Pivot Tables

  • Thread starter Thread starter Tom
  • Start date Start date
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.
 
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
'=======================
 
Back
Top