CODEWITHSUNDEEP
vbaexcel-2010
User
User

Reputation: 363

VBA Excel get max value from pivot table

I am trying to grab the max value in a pivot table and display the corresponding row.

For example

Row Label | Sum of Resource

row 1: 22

row 2: 30

row 3" 15

It will display row 2 since 30 is the largest value.

What I have is here

Function getMaxPT()
Dim pt As PivotTable
Dim max As Integer
Dim PTfield As PivotField

Set pt = Worksheets("Sheet").PivotTables("PivotTable1")

For Each PTfield In pt.RowFields
    Debug.Print PTfield.Name
Next PTfield
End Function

PS. I am using excel 2010 on windows 7

Upvotes: 2

Views: 4203

Answers (3)

Mauricio Roa
Mauricio Roa

Reputation: 1

Just another little implementation of a function to get the max of a specific column on a pivot table, based on previous answers. You can pass the pivot table name and the field name.

Function getMaxPT(istrPTName As String, istrPTField As String) As Double
  ' Found the max value in a column from a pivot table
  ' Author: Mauricio Roa, [email protected]
  ' Date: 2018-10-02

  Dim myRange As Range
  Dim pt As PivotTable
  Dim ws As Worksheet
  Dim bPTFounded As Boolean

  ' Search for the pivot table using the name
  bPTFounded = False
  For Each ws In ActiveWorkbook.Worksheets
      For Each pt In ws.PivotTables
          If pt.Name = istrPTName Then
              bPTFounded = True
              Exit For
          End If
      Next pt
      If bPTFounded Then Exit For
  Next ws

  ' If founded, calculate the maximum. If not found, result is undefined
  If bPTFounded Then
      Set myRange = pt.PivotFields(istrPTField).DataRange
      getMaxPT = Application.WorksheetFunction.max(myRange)
  End If
End Function

Sub getMaxPTTest()
    Dim x As Double

    x = getMaxPT("myPivotTable", "myPTColumnName")
End Sub

Upvotes: 0

QHarr
QHarr

Reputation: 84465

You can avoid a loop and refer to the datarange of the column if only interested in one column (assuming subtotals are off and single column row fields)

Public Sub test()
    Dim localMax As Long, myRange As Range, found As Range
    Dim pvt As PivotTable
    Set pvt = Worksheets("Sheet").PivotTables("PivotTable1")
    Set myRange = pvt.PivotFields("Sum of Resource").DataRange
    localMax = Application.WorksheetFunction.Max(myRange)
    Set found = myRange.Find(localMax)
    Debug.Print localMax, Worksheets("Sheet").Cells(found.Row, pvt.RowRange.Column)
End Sub

Data:

data

Results:

localMax = 19; b (row = 5)

For all columns, not just one, if subtotals and grandtotals are off, use

Set myRange = Worksheets("Sheet").PivotTables("PivotTable1").DataBodyRange

For all columns with grand total, but no subtotals:

Set myRange = Worksheets("Sheet").PivotTables("PivotTable1").DataBodyRange
Set myRange = myRange.Resize(myRange.Rows.Count - 1, myRange.Columns.Count)

Upvotes: 1

Vityata
Vityata

Reputation: 43585

To get the values of a specific field, you should loop through its cells. Then make some comparison to take the maximum. This returns the maximal value of all RowFields in the PivotTable:

Function getMaxPT()

    Dim pt As PivotTable
    Dim max As Double
    Dim PTfield As PivotField
    Dim var As Variant

    Set pt = Worksheets(1).PivotTables("PivotTable1")

    For Each PTfield In pt.RowFields
        For Each var In PTfield.DataRange.Cells
            If max < var Then max = var
            Debug.Print var
        Next var
    Next PTfield

    MsgBox max

End Function

Upvotes: 0

Related Questions