VBA - Activating Cells with data and following spaces - Loop

Experts,

I want to apply different formatting to the Data within my pivot table. Within RNG I want to isolate each entry, including the spaces after it, see attached.

For PLAN A, I would want to activate G5:G6, then do something IF/ELSE, etc., Then go to next value - PLAN B, activating G7:G8, PLAN C, G9:G12, through to the end of RNG.

Would I loop through the cells with end(x1up), then go down a cell - end(x1up) and so on?

Thank you in advance,
Kevin
EE-Go-Through-Range-Within-Pivot.xls
LVL 23
redrumkevAsked:
Who is Participating?
 
Rory ArchibaldCommented:
Like so:
   Dim pt As PivotTable, pf As PivotField, pi As PivotItem
   Set pt = ActiveSheet.PivotTables(1)
   Set pf = pt.RowFields(1)
   For Each pi In pf.VisibleItems
      ' do whatever formatting you need here
      Debug.Print pi.LabelRange.Address
   Next pi

Open in new window

0
 
redrumkevAuthor Commented:
rorya,

That does a great job of it - and avoids my range selection from before.

Question - how would I call the pi to be formatted (borders).

I have used the following in the past:
 With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
    End With

Open in new window


I tired With Pi.Borders(xlEdgeBottom) ... End With

Kevin

0
 
Rory ArchibaldCommented:
You need pi.labelrange rather than just pi
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
redrumkevAuthor Commented:
THANKS AGAIN - THIS WAS PERFECT!!!
0
 
redrumkevAuthor Commented:
rorya,

It was working fine - but after I refreshed the pivot, I now get an error (1004):

Unable to get the LabelRange property of the PivotItem Class.

It happens on:

PI.LabelRange.BorderAround _
Weight:=xlMedium

Open in new window


Attached is the test sheet.

Kevin
EE-Go-Through-Range-Within-Pivot.xls
0
 
redrumkevAuthor Commented:
Rorya,

I see what the problem is, I have a pivot table that has PLAN names (column A, PT.RowFields(1) ) which are checked to show, however there is NO DATA for them at this time. So they are "out of range" because no data exist for them. How do I get around this. You had "For Each PI In PF.VisibleItems" so I assumed that this would only go through those that had data.

I am going to ask a new question for this, I am going to post the link back here for you in a few minutes.. so If you have a solution, please post over there so you can get the points.

Thank you,
Kevin
0
 
redrumkevAuthor Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.