Excel 2003 vba hiding totals for some pivot items and not others

[alok.work]

Dear Group,

I have a somewhat strange request. I would like to hide some subtotals
and not others in a pivot table.

The following code will generate a pivot table summary of some
hypothetical sales records. Salesmen A sells both chairs and tables,
and salesman B sells tables in region A and B, so the salesmen level
totals are meaningful. However, salesmen C and D only sell tables in
one region, so the total is not informative. However, I don't want to
hide the details since then I also lose the details that C and D sell
tables, and in which region. What I want to do is hide to totals, but
keep the details, which is the opposite of the usual thing.

Furthermore, in some cases, I will want to hide the totals for
salesmen like A, since I will only want totals when there are multiple
entries in the first subcategory.

This may not seem important, but imagine that there are a lot of
salesmen, and many more salesmen are of type C and D, so it wastes
rows, and that there are many items and regions and it is important to
know which a given salesman handles, and we want to print it all out
and not have lots of nearly identical total rows.

Is there a way do this from VBA?

thanks,

code follows:

Sub makeDataset()
Range("A1").Select
ActiveCell.FormulaR1C1 = "Salesman"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Region"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Item"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Cost"

Range("A2:A1000").Select
Selection.FormulaR1C1 = _
"=CHOOSE(MOD(Row(), 4)+1, ""A"",""B"",""C"",""D"")"

Range("B2:B1000").Select
Selection.FormulaR1C1 = _
"=CHOOSE(MOD(Row(), 8)+1,
""north"",""south"",""east"",""east"",""north"",""north"",""east"",""east"")"

Range("C2:C1000").Select
Selection.FormulaR1C1 = _
"=CHOOSE(MOD(Row(), 8)+1,
""chair"",""table"",""table"",""table"",""table"",""table"",""table"",""table"")"

Range("d2:d1000").Select
Selection.FormulaR1C1 = _
"=CHOOSE(MOD(Row(), 4)+1, 20,20,20,20)"
End Sub
Sub createPivotTable()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase,
SourceData:= _
"Sheet1!R1C1:R1000C4").createPivotTable TableDestination:="",
TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Salesman")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Region")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Item")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField
ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Cost"), "Sum of Cost", xlSum
ActiveWorkbook.ShowPivotTableFieldList = False
Range("G9").Select

ActiveSheet.PivotTables("PivotTable2").PivotFields("Region").Subtotals
= Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)
End Sub

 

[David Portwood]

I just had a similar problem in a PT. I added a calculated item representing
the percent change in a Year-Over-Year analysis. My PT was based on summed
data and the sum of such a column is meaningless so I wanted the totals to
disappear for that column only.

I resolved the problem by manually setting the font color in the appropriate
cells to the same color as the background (white). The totals seemed to
disappear - but of course they didn't. You may be able to do the same thing
programmatically. Or, if your structure remains the same no matter how much
data is added or removed, you can check Preserve Formatting and do it
manually as well.

 

[steveh]

What you can do is hide any row where the cell above the total has a
value (Grand Total being an exception). For instance, D Total has D
above it, so the row with D Total can be hidden. Similarly for C Total,
but not B and A Total. As you might have noticed, I would work my way up
in the VBA loop.

I'm sure you've considered it, but this report just screams wanting to
have the Salesman or the Region and Item in the column instead of all
three in the row. That would solve your problem as well.

Steve

 

[alok.work]

Unfortunately, I think there are far too many salesmen, regions and
items in the real problem- there would be dozens of columns either
way.

I think that selectively hiding rows may be the way to go. Thanks for
suggesting a very simple strategy to accomplish this, one that does
not depend upon the structure of the pivot table.