Pivot Table - Hiding 0 totals

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JohnV - 30 Dec 2003 23:25 GMT

I have data for individuals for each week. I want to hide
all rows that have 0.00 for their weekly total:

Employee Project Month Hours
100201 10501 2/1/2003 42.0
3/1/2003 0.0
4/1/2003 50.0
100263 10501 2/1/2003 42.0
3/1/2003 10.0
4/1/2003 50.0

Is their a way to filter the table so that the results
look like:

Employee Project Month Hours
100201 10501 2/1/2003 42.0
4/1/2003 50.0
100263 10501 2/1/2003 42.0
3/1/2003 10.0
4/1/2003 50.0

Regards,
JohnV

Reply to this Message

Debra Dalgleish - 30 Dec 2003 23:37 GMT

The following macro by John Green will hide the rows which total zero:

'=====================================
Sub HideZeroRows()
'hide worksheet rows that contain all zeros
'by John Green
Dim rRow As Range

For Each rRow In ActiveSheet _
.PivotTables(1).DataBodyRange.Rows
If Application.Sum(rRow) = 0 Then
rRow.EntireRow.Hidden = True
Else
'DD--I added this to unhide
'any previously hidden rows
rRow.EntireRow.Hidden = False
End If
Next rRow
End Sub
'===================================

> I have data for individuals for each week. I want to hide
> all rows that have 0.00 for their weekly total:
[quoted text clipped - 16 lines]
> 3/1/2003 10.0
> 4/1/2003 50.0

Signature

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

Reply to this Message

JohnV - 31 Dec 2003 17:25 GMT

Thanks for the reply. The macro addresses rows where
there is a 0.0. The problem is that the data that drives
the pivot table can consist of 2 or more rows that when
summed equals 0.0.

The information I provided below is the summation that the
pivot table gives, not the invididual row of the data.
What I am looking for is some way to hide the rows in the
pivot table where the summation equals 0.0 for the 'row
criteria'.

Any suggestions will be appreciated.

JohnV

>-----Original Message-----
>The following macro by John Green will hide the rows which total zero:
[quoted text clipped - 38 lines]
>> 3/1/2003 10.0
>> 4/1/2003 50.0

Reply to this Message