We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

redrumkev
redrumkev asked
on
Medium Priority
678 Views
Last Modified: 2012-05-11
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
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
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

CERTIFIED EXPERT

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
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?
CERTIFIED EXPERT

Author

Commented:
Rory,

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

Have a good weekend,
Kevin
CERTIFIED EXPERT

Author

Commented:
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.