Selection of last 3 values (weeks) in pivot table but without 'blank' with VBA

user_10419493 0
Viewed 473

I have a report containing multiple pivot tables.

I found the below macro that selects number of required weeks (in my case I need to always select last 3 weeks) in one of the pivot tables. It does select last 3 values, however the problem is, it also selects 'blank' field.

The code is as follows:

Sub ShowLastXWeeks()
Dim pi As PivotItem
Dim lLoop As Long
Dim pt As PivotTable
Dim lCount As Long
Dim lWeeks As Long

On Error Resume Next
lWeeks = 3
If lWeeks = 0 Then Exit Sub

Application.ScreenUpdating = False
Set pt = ActiveSheet.PivotTables("PivotTable1")

        For Each pi In pt.PivotFields("Week").PivotItems
            pi.Visible = False
        Next pi


    With pt.PivotFields("Week")
        For lLoop = .PivotItems.Count To 1 Step -1
            .PivotItems(lLoop).Visible = True
            lCount = lCount + 1
            If lCount = lWeeks Then Exit For
        Next lLoop
    End With

On Error GoTo 0
Application.ScreenUpdating = True
End Sub

My pivot table is as follows:

enter image description here

enter image description here

I tried to get rid of 'blank' value but without success.

How I can modify the above script to omit the 'blank' field and only select last 3 weeks?

Thanks in advance.

pivot-table vba excel
1 Answers

I have found the solution to my own problem, so I hope I will help anybody else struggling with the same problem. I got rid of the 'blank' value by adding the below to my code:

Set pf = pt.PivotFields("Week")
With pf
On Error Resume Next
    .PivotItems("(blank)").Visible = False
On Error GoTo 0
End With

The whole code is as follows:

Sub ShowLastXWeeks()
Dim pi As PivotItem
Dim lLoop As Long
Dim pt As PivotTable
Dim lCount As Long
Dim lWeeks As Long

On Error Resume Next
lWeeks = 4
If lWeeks = 0 Then Exit Sub

Application.ScreenUpdating = False
Set pt = ActiveSheet.PivotTables("PivotTable1")

    For Each pi In pt.PivotFields("Week").PivotItems
        pi.Visible = False
    Next pi


With pt.PivotFields("Week")
    For lLoop = .PivotItems.Count To 1 Step -1
        .PivotItems(lLoop).Visible = True
        lCount = lCount + 1
        If lCount = lWeeks Then Exit For
    Next lLoop
End With

On Error GoTo 0

Set pf = pt.PivotFields("Week")
With pf
On Error Resume Next
    .PivotItems("(blank)").Visible = False
On Error GoTo 0
End With

Application.ScreenUpdating = True

End Sub

Thanks

user_104194930
Related