I could achieve what I wanted. I simple recorded first a Macro in order to see the details which objects have to be how accessed. Afterwards I finished the thing by making some optimization. Please refer below to the code extract, maybe helpful for others.
Regarding the ordering I found in a forum the tip that you should assing another column "sort order" in the origin data table and used it as hidden column in the pivot table so you can define the sort order by using that field. This one get also not lost when you later change the data.
Sub Macro1()
On Error GoTo Fehler
Application.ScreenUpdating = False
With ActiveWorkbook.Sheets("Rating").PivotTables(1)
.PivotFields("OWASP 2010").ClearAllFilters
With .PivotFields("OWASP 2010")
If .PivotItems("(blank)").Visible = True Then
.PivotItems("(blank)").Visible = False
End If
' uncomment this sectin in case you liek to change the sorting via vba
' 1. sort first the stuff automatically A-Z
'.AutoSort xlAscending, "OWASP 2010"
' 2. move empty values to the end
'If .PivotItems(" ").Position <> .PivotItems.Count - 1 Then
' .PivotItems(" ").Position = .PivotItems.Count - 1
'End If
End With
.PivotFields("Categories").ClearAllFilters
With .PivotFields("Fortify Categories")
If .PivotItems("0").Visible = True Then
.PivotItems("0").Visible = False
End If
If .PivotItems("(blank)").Visible = True Then
.PivotItems("(blank)").Visible = False
End If
End With
End With
Application.ScreenUpdating = True
Exit Sub
Fehler:
Application.ScreenUpdating = True
End Sub
regards
Mark
I am tried to use a macro recording for modifying the filter. But while I try to execute later the macro it responds with the error "Unable to set the Visible property of the PivotItem class". Any thoughts?
ActiveSheet.PivotTables("PivotTable6").PivotFields("OWASP 2010").PivotItems("(blank)").Visible = False
– megloff – 2014-09-05T15:21:24.823