CODEWITHSUNDEEP
vbaexcel
GCC
GCC

Reputation: 295

Selecting a cell within the last row of a pivot table

I am trying to write a macro that gives me the value of cell D of the last row within a pivot table. This is the code I have so far.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim PT As PivotTable
Dim Can As Worksheet
Dim LastRow As Range

Set Can = Worksheets("Can")
Set PT = Can.PivotTables("Can Table")

Set Flight1 = Can.Range(("D14"), ("D" & Rows.Count)).End(xlDown)

Can.Range("AC15") = Application.WorksheetFunction.Sum(Flight1)

End Sub

This macro works fine until I start playing around with the pivot table. Since there are some cells within the pivot table that have no value, the code I have right now will identify the last row up until the no value cell. What I want is the very last row of the pivot table (aka the "Grand Total"). How do I choose the last row of the pivot table to disregard any cell within the pivot table that may have no value?

Thanks

G

Upvotes: 1

Views: 4527

Answers (2)

QHarr
QHarr

Reputation: 84465

You can use the pivottable TableRange1 property

Adapt the below as required

Option Explicit

Sub test()

    Dim PT As PivotTable
    Dim Can As Worksheet
    Dim Flight1 As Range
    Dim lastRow As Long

    Set Can = ThisWorkbook.Worksheets("Can")

    With Can

        Set PT = .PivotTables("Can Table")    'change as appropriate

        lastRow = .Range(Split(PT.TableRange1.Address(0, 0), ":")(1)).Row

        If lastRow >= 14 Then
            Set Flight1 = Can.Range("D14:D" & lastRow)
        Else
            Debug.Print "Last row is before start row of 14"
        End If

    End With

    If Not Flight1 Is Nothing Then Debug.Print Flight1.Address

End Sub

Upvotes: 1

mooseman
mooseman

Reputation: 2017

Finding the last row is very shaky using End(xlDown) Try

("D" & Rows.Count).End(xlUp)

The only problem I could see id if you had data below your pivot table

Upvotes: 0

Related Questions