Select rows in pivot table with macro

[Dolphinv4]

Hi,

I have a pivot table as follows:

Doc Tx Amount
123 12 5
124 11 2
125 12 3
(blank) (blank)
Grand Total 10

I need a macro to select the cells in the 2nd row (row just below the
header) - only 3 columns and not the entire row - up to the row just before
the row with (blank).

How do I do that?

Thanks,

Dolphinv4

 

[Tom Hutchins]

Paste this macro into a VBA module in your workbook. Make sure the sheet with
the pivot table is the active sheet, then run the macro (Tools >> Macro >>
Macros >> SelPivotTblCells >> Run).

Sub SelPivotTblCells()
Dim Rng As Range, EndRow As Long, c
Set Rng = ActiveSheet.PivotTables(1).RowRange
Rng.Cells(2, 1).Activate
Set c = Rng.Find(What:="(blank)", LookIn:=xlValues)
If c Is Nothing Then
MsgBox "(blank) not found"
Exit Sub
End If
Range(Rng.Cells(2, 1), Cells(c.Row - 1, 3)).Select
Set Rng = Nothing
Set c = Nothing
End Sub

Hope this helps,

Hutch