Excel 2010 VBA: Last Row in Pivot Table

Please Login or Register  to view this content.

Hi All

I am trying to assign the last row of a Pivot Table to a variable.

I have tried the two statements above in the immediate window that I found on another thread, but it is not always referencing the last row.

Help!

I personally use this, but depending on what you have in your pivot table, the last row of may have a different name from "Grand Total"

Please Login or Register  to view this content.

Hi All

I just get the last used row with the first method which is often well beyond the last row of the Pivot
The 2nd method, doesn't seem to work everytime either, not sure why. I have multiple worksheets each with just one pivot on, and when I run this in the immediate window it works for some and not others.

Anything more robust?

Well I use it with 0 problems. You probably didn't change the column letter to the column where your pivot table starts. Anyway, if the other method works better, then just use that.

If it's only working sometimes, then are you sure you are referencing the correct pivot table? In case you are not sure, PivotTables(1) references only the first pivot table in your worksheet.

Thanks All

You were right! I had the wrong Column Letter!

Glad you fixed it.

Originally Posted by romperstomper

Tablerange1 refers to the range of the Pivottables at the time. No ifs ands or buts. If you can provide something that shows otherwise I will be happy to bug it, but otherwise you are incorrect.

Who were you talking to?