1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
'The "Valid Percent" column can be hidden using a script, As discussed In
'Solution 10000634, "Hiding a column such as 'Frequency' in PivotTable
'Output".  Just call the HideColumn subroutine with "Valid Percent" instead
'of "Frequencies".

'The "Valid" column Is trickier, because it Is really a Group Label, And
'cannot be hidden without hiding All the rows In the group.  This has
'disastrous consequences As it hides everything but the Total.  I have
'appended a subroutine, Ungroup, which can be used To make the Group Label go
'away.  Note that it Is gone For good, Not just hidden.

'For example, somewhere In your Autoscript Or Sub Main, you would have:

'        HideColumn objPivot, "Valid Percent"
 '       Ungroup objPivot, "Valid"



'John Bauer, Ph.D.
'SPSS Developer Support



'---------------------------------------------------------------
' Add to script, e.g. Solution 100006374
'---------------------------------------------------------------
Sub Ungroup(objPivot As PivotTable, strGroupLabel As String)
        Dim objRowLabels As ISpssLabels
        Dim i As Long, j As Long
        Dim Found As Boolean

        Set objRowLabels = objPivot.RowLabelArray
        With objRowLabels
                For j = 0 To .NumColumns - 1
                        For i = 0 To .NumRows - 1
                                If Not IsNull(.ValueAt(i, j)) Then
                                        If .ValueAt(i, j) = strGroupLabel
Then
                                                .SelectLabelAt(i, j)
                                                Found = True
                                                Exit For
                                        End If
                                End If
                                If Found Then Exit For
                        Next
                Next
        End With
        objPivot.Ungroup
End Sub