Trying to loop through data in the second column of a pivot table. Stuck. Need Help.

[pawest]

Hi,
I have a pivot table with two columns: Code and Quantity. I want to loop through the Quantity column and hide all values = 0. I can do this easily by incorporating this into my code:

    pt.PivotFields("Code").PivotFilters.Add _
        Type:=xlValueDoesNotEqual, DataField:=pt.PivotFields("Sum of Quantity"), Value1:=0

However, I have a "(blank)" code in all pivot tables. I do this because occasionally there is no data to organize in a pivot table. Having a "(blank)" option will allow my code to work even when there's no data. Therefore, I want to keep blank visible at all times. The above code hides "(blank)", however.

The below code is what I've tried with no success. Any alterations to the below code or additional suggestions would be great!

Set pf = pt.PivotFields("code")
    For Each pi In pf.PivotItems
        If pi.PivotFields("Sum of Quantity").Value = 0 Then
                pi.Visible = False
        Else
                pi.Visible = True
        End If
    Next pi
End With

Thanks!

 

[pawest]

By the way, the "end with" in the above code should not be there.

I'm still trying to find a solution.

 

[pawest]

I tried this as another solution but still no luck:

Set pf = PT.PivotFields("code")
    For Each pi In pf.PivotItems
    
        If pi.Name = "(blank)" Then
                pi.Visible = True
        ElseIf pi.ChildItems <> 0 Then
        
                pi.Visible = True

        Else

                pi.visible = false        

        End If
    Next pi

Any body know how to filter out values one-by-one within the 2nd column of a pivot table within a for each statement? Thanks.

 

[pawest]

I came up with a work around... it is a work around that I do not like as I end up deleting pivot table data and not utilizing the capabilities of a pivot table. However, I don't properly exit the loop and it keeps looping forever.

    'Set move after return to up to process the pivot table data
    Application.MoveAfterReturnDirection = xlUp
       
    'Deleting cells within the pivot table = 0
    lastRow = Cells(Rows.Count, 17).End(xlUp).Row
            
        Do
        
            For Each cell In Range("P13:P" & lastRow)
        
                If cell.Value = "(blank)" Then
                    lastRow = cell.Offset(1, 0).Row
                    ReDim myArray(1 To lastRow)
                    
                    
                ElseIf cell.Value = "Grand Total" Then
                    lastRow = cell.Row
                    ReDim myArray(1 To lastRow)
                
                    
                ElseIf cell.Offset(0, 1).Value = 0 Then
                    cell.Delete
                    ReDim myArray(1 To lastRow) 'when I include this line, my code tries exiting too early and gets stuck at '***
                                                            'when I don't include this line, my code works but loops forever and never exits



                End If
    
            Next cell
            
        Loop Until cell.Value <> 0  '***
    
    'Set move after return to down as normal
    Application.MoveAfterReturnDirection = xlDown

I have the myArray variable redim to a new array size each time the pivot table size changes, the cell value equals (blank), or the cell value equals Grand Total.

Still working to determine a solution. Would appreciate some feedback.

 

[pawest]

This is my solution. It would be great if someone could improve this even further or relay an even better solution:

    counter = 0

    'Set move after return to up to process the pivot table data
    Application.MoveAfterReturnDirection = xlUp
       
    'Deleting cells within the pivot table = 0
    Lastrow = Cells(Rows.Count, 17).End(xlUp).Row
            
        Do
        
            For Each cell In Range("P13:P" & Lastrow)
        
                If cell.Value = "(blank)" Then
                    Lastrow = cell.Offset(1, 0).Row
                    ReDim myArray(1 To Lastrow)
                    
                    
                ElseIf cell.Value = "Grand Total" Then
                    Lastrow = cell.Row
                    ReDim myArray(1 To Lastrow)
                
                    
                ElseIf cell.Offset(0, 1).Value = 0 Then
                    cell.Delete
                    ReDim myArray(1 To Lastrow)

                End If
    
            Next cell
            
            counter = counter + 1
            
        Loop Until counter = 5
    
    'Set move after return to down as normal
    Application.MoveAfterReturnDirection = xlDown

Thanks!