asked on

VBA - Pivot Table For Each Out of Range due to "unused values"

Experts,

See attached, where PivotTable1 had some formatting:

    Set PT = ActiveSheet.PivotTables(1)
Set PF = PT.RowFields(1)
For Each PI In PF.VisibleItems
PI.LabelRange.BorderAround _
Weight:=xlMedium
Debug.Print PI.LabelRange.Address

Open in new window

My RowFields(1) Had the following Plans:
PLAN A
PLAN B
PLAN C
PLAN D
PLAN E

Now I have updated my data (Sheet1!$D$2:$E$8) and this time I only have:
PLAN A
PLAN C
PLAN D
PLAN E

Stepping through, the code works for PLAN A, but then "can't locate" PLAN B, so it errors, message 1004 - Unable to get the LabelRange property of the PivotItem Class.

How do I go through the LableRange but only those that have a value. I thought that the "PF.VisibleItems" would have covered this.

Thank you,
Kevin

Option Explicit
Sub FormatSalesByProduct()

Dim PT As PivotTable
Dim PF As PivotField
Dim PI As PivotItem

    Set PT = ActiveSheet.PivotTables(1)
    Set PF = PT.RowFields(1)
        For Each PI In PF.VisibleItems
            PI.LabelRange.BorderAround _
                Weight:=xlMedium
        Debug.Print PI.LabelRange.Address
    Next PI

    Set PT = ActiveSheet.PivotTables(1)
    Set PF = PT.RowFields(2)
        For Each PI In PF.VisibleItems
            PI.LabelRange.BorderAround _
                Weight:=xlMedium
        Debug.Print PI.LabelRange.Address
    Next PI

End Sub

Open in new window

EE-PivotTable-Error-Missing-Row1.xls

Rory Archibald

Try:

Sub FormatSalesByProduct()

Dim PT As PivotTable
Dim PF As PivotField
Dim PI As PivotItem

    Set PT = ActiveSheet.PivotTables(1)
    Set PF = PT.RowFields(1)
    On Error Resume Next
    For Each PI In PF.VisibleItems
        PI.LabelRange.BorderAround Weight:=xlMedium
    Next PI

    Set PF = PT.RowFields(2)
    For Each PI In PF.VisibleItems
        PI.LabelRange.BorderAround Weight:=xlMedium
    Next PI

End Sub

Open in new window

redrumkev

ASKER

Rorya,

That worked, to skip the errors, but after changing things (see I changed the PLAN TYPES) around and refreshed the table, now ABC (H5:H6) the formatting is not working.

Attached -->
Debug.Print PI.LableRange.Address shows:

$G$5:$G$6
$G$7:$G$10
$G$11:$G$12
$G$13:$G$14
$H$5:$H$8,$H$11:$H$14
$H$9:$H$10

So column G is correct.
But column H should be:
$H$5:$H$6
$H$7:$H$8
$H$9:$H$10
$H$11:$H$12
$H$13:$H$14

Do I need to start over again after updating RowFields(1) is that why Excel see's the range differently???

Kevin
EE-PivotTable-Not-All-Items-Bord.xls

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

redrumkev

ASKER

Rory,

THAT WORKED!!

Can you please help me to understand the second For Each Statement:

I understand that there are two variables for PivotItem with the second using PI2. But what (how and why) does the Intersect function do? And why does it reference back to PI1?

Kevin

Rory Archibald

For each item in field 1 it loops through each item in field 2 then adds borders for the label range in field2 that relates to field 1. This takes care of the cases where the same item in field2 appears consecutively for 2 different items in field 1 - otherwise the code would treat field 2 as one contiguous range and only border round the outside of the whole block. Make sense?

redrumkev

ASKER

Rory,

Yes, that does make sense, thank you for explaining it!!!

Have a good weekend,
Kevin

redrumkev

ASKER

Rory,

Add on to this question:
https://www.experts-exchange.com/questions/27018811/VBA-Pivot-Table-Formatting.html

Kevin

VBA - Pivot Table For Each Out of Range due to &quot;unused values&quot;

VBA - Pivot Table For Each Out of Range due to "unused values"