Hiding rows containing zeroes or blanks in pivot tables?
 

How do I hide rows containing zeroes or blanks in pivot tables?

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

ChrisBusch wrote:
How do I hide rows containing zeroes or blanks in pivot tables?



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thanks Debra. Can provide some direction on how to implement this code?
I've not done any Excel programming yet.


"Debra Dalgleish" wrote:

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

ChrisBusch wrote:
How do I hide rows containing zeroes or blanks in pivot tables?



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html