[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBA - Activating Cells with data and following spaces - Loop

Posted on 2011-04-20
7
Medium Priority
?
315 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:redrumkev
  • 5
  • 2
7 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 35434310
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
 
LVL 23

Author Comment

by:redrumkev
ID: 35434411
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
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 2000 total points
ID: 35434551
You need pi.labelrange rather than just pi
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 23

Author Closing Comment

by:redrumkev
ID: 35434648
THANKS AGAIN - THIS WAS PERFECT!!!
0
 
LVL 23

Author Comment

by:redrumkev
ID: 35435592
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
 
LVL 23

Author Comment

by:redrumkev
ID: 35435726
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
 
LVL 23

Author Comment

by:redrumkev
ID: 35435800
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question