Selecting a datarange based on the value of a pivot item

RJT_VT used Ask the Experts™
I have a pivot table (image below) with three row fields. I would like to select a data range using the value of the second row field, and then format the selected range based on that value. The problem is when I iterate through the pivot items in the second row field, my code lists all the items in the field, not the one item associated with rowfield (1) value.  I can't seem to search based on a hierarchy like this:

for each item in rowfield(1)
    if rowfield(2).pivotitem value = "x" then
         select and format for x
          leave format alone
  end if

Using an index to the pivot items of rowfield two doesn't return only the item for that field, it returns all values in the domain for the field. I'd like to base the action on the value of the item in the second row field, but I can't seem to query the value of that item alone.
pt image
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I see this question has been unanswered. Do you still need help ? If yes then can you post a sample workbook that have the data you pictured ?



The problem has not been solved. Attached is a workbook with a similar pt, and data.
Note that the pivot table here is based on a recordset extract of the data in the sheet named FMOutput, I apologize for not responding sooner. I didn't realize there was a comment.
ok lets agree then that if you want a solution we should keep the pace faster and more prompt. Just check your email and when there is a comment on one of the question you asked there should be an email sent (maybe it is in your junk mail check there too.)

Pls confirm understanding basis which I will then look at this more in details.
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.


Confirmed. Believe me, I'm anxious for a solution. I have no record of an email from your 03/06 comment. I logged on today expecting to close the request.
ok fine no problem. Did you check now ?? you should have an email with my last reply and this one as well. If you still did not receive anything, I suggest you bring this to customer service they will look it up for you.

Will look at your file.


I did get your responses via email. Thank you.
ok great.

Letts get to specifics now.

You mention in your post:
I would like to select a data range using the value of the second row field, and then format the selected range based on that value.

In you sheet you have
Pulp (Cords)
Sawlog (BF)

which one you want to iterate ?


It is the Unit row field, not the column fields.  I'd like to format the ranges differently...cords with tenths and BF with round numbers...but first I need to select the data range. I could do this by iterating through and selecting based on a string search of Unit pivot item, but there must be an easier way.
Sorry I need to get some things done will be back on this in few hours. meantime pls state specifically based on the file you posted what are the lines or circumstances you need to format and how

Will be back
our replies just crossed.

Not sure I understand please be more specific as to what data need to become what then when I understand what you want I can lead you to how to do it.


As I state in my initial post, I want to use VBA to select a data range associated with a single pivot item value, say, Units=cords, where Units is a row field with pivot items for each type of unit (cords, bf, etc). I can select manually, but it does not work to simply record a macro. Once a data range is selected, I simply want to modify the number format.

Look over my initial problem statement. "Units" is the second row field. Is there a way to use VBA to select the data range associated with Units= "cords"?  

I can iterate through the rowfields, but I'm having trouble iterating through the values of a pivot item. As stated above,...Using an index to the pivot items of rowfield "Units" doesn't return a single item for that field---the current pivotitem value where i can test for unit= "cords"--, it returns all values in the domain of item values for the field. I'd like to base the action on the value of the item in the second row field, but I can't seem to query the value of that item alone.
ok sorry for delay due to time zone diff.

here is some code that may help you get what you want (all is in the attached workbook)

1) to iterate thru all items in the pivot table Tbl

Dim Tbl As PivotTable

'---> Iterate thru Pivot items Row/Col
        For I = 0 To Tbl.PivotRowAxis.PivotLines.Count
            For J = 1 To Tbl.PivotColumnAxis.PivotLines.Count
                Debug.Print "Col " & J & " Row " & I & " " & Tbl.ColumnRange.Cells(I, J)
            Next J
        Next I

Open in new window

2) To iterate thru a specific field

Dim Tbl As PivotTable
Dim PField As PivotField
Dim PItem As PivotItem

'---> Iterate thru Fields
        For Each PField In Tbl.PivotFields
            Debug.Print PField.Caption
            If PField = "Unit" Then
                '---> Iterate thru specific item
                For Each PItem In PField.PivotItems
                    For Each cCell In PItem.DataRange
                        Debug.Print "Item  " & PItem.Caption & " value " & PItem.Value & " Cell " & cCell.Address & " Value " & cCell.Value
                    Next cCell
                Next PItem
            End If
        Next PField

Open in new window

In the attached WB you have a sub called test all the data will display in the immediate window. I suggest you runt the code by step F8 then F8 so you see the instructions running and the results being printed in the immediate window.

Let me know if this helps and you need something more specific.


I was missing the looping through the item caption (somehow!)
Here's what I developed from your suggested code:
Dim cCell As Range
    Dim PItem As PivotItem
    With PT.PivotFields("Unit")
         For Each PItem In PT.PivotFields("Unit").PivotItems
              For Each cCell In PItem.DataRange
                  If PItem.Caption = "tons" Then   'once the desired item caption is found...
                    PItem.DataRange.Select         '... select the data range and format
                    Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""?_);_(@_)"
                    Exit For                        'ignore any other cells in the item--they're all the same
                  End If
              Next                                    'next item
     End With

Open in new window

Thank you!  I'm more than happy to award points, but if this is easy: the selection doesn't include the total row and total column. I can use .offset or .entirerow to extend, but I don't see an easier method. Any suggestion?
Well I tried, but frankly not too much in depth in Pivots so presume easiest would be to use offset just after you finish looping with the item.

I am sure there must be an easier way, ... but  :(
If I find something will let you know in here.

ok got it !!

This should select all the Subtotals in the Pivot table Tbl

Tbl.PivotSelect "'" & PItem.Name & "' '" & PField.Name & "'[All;Total]", xlDataAndLabel, True

after selection is done apply something like
Selection.Font.Bold = True
or ...
Selection.Font.ColorIndex = 3
will color in Red all the Data of the Subtotals



Thanks a bunch!
Your welcome. You can post a link in this question for any other question you may need help with, I will keep it monitored.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial