I'd like opinions on which is more efficient in a macro.

Problem: When using a pivot table, a macro needs to know
the last row. "ActiveSheet.Cells.SpecialCells
(xlCellTypeLastCell).Row" does not get updated when the
pivot table gets filtered. In other words, if the table
starts out at 2000 rows and then is filtered to 10 rows,
the SpecialCells value still says 2000. But after using
UsedRange, the SpecialCells value does get updated.

Which method do you think is more efficient:
1) ActiveSheet.UsedRange.Row +
ActiveSheet.UsedRange.Rows.Count

2) Range("A65536").End(xlUp).Row

Or any other thought on getting the last row of a pivot
table?

Thank You!!

Hi Norm,

I would always use

Range("A65536").End(xlUp).Row

or more specifically

Range("A" & Rows.Count).End(xlUp).Row

as that will always work. UsedRange does not always automatically update.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Norm" wrote in message
...
I'd like opinions on which is more efficient in a macro.

Problem: When using a pivot table, a macro needs to know
the last row. "ActiveSheet.Cells.SpecialCells
(xlCellTypeLastCell).Row" does not get updated when the
pivot table gets filtered. In other words, if the table
starts out at 2000 rows and then is filtered to 10 rows,
the SpecialCells value still says 2000. But after using
UsedRange, the SpecialCells value does get updated.

Which method do you think is more efficient:
1) ActiveSheet.UsedRange.Row +
ActiveSheet.UsedRange.Rows.Count

2) Range("A65536").End(xlUp).Row

Or any other thought on getting the last row of a pivot
table?

Thank You!!

set rng = ActiveSheet.PivotTables(1).TableRange2

lastrow = rng.rows(rng.rows.count).row

xlCellTypeLastCell uses UsedRange, so if one doesn't meet your expectations,
neither will.

--
Regards,
Tom Ogilvy

"Norm" wrote in message
...
I'd like opinions on which is more efficient in a macro.

Problem: When using a pivot table, a macro needs to know
the last row. "ActiveSheet.Cells.SpecialCells
(xlCellTypeLastCell).Row" does not get updated when the
pivot table gets filtered. In other words, if the table
starts out at 2000 rows and then is filtered to 10 rows,
the SpecialCells value still says 2000. But after using
UsedRange, the SpecialCells value does get updated.

Which method do you think is more efficient:
1) ActiveSheet.UsedRange.Row +
ActiveSheet.UsedRange.Rows.Count

2) Range("A65536").End(xlUp).Row

Or any other thought on getting the last row of a pivot
table?

Thank You!!

Or any other thought on getting the last row of a pivot
table?

Would this idea work independent of the Used Range?

Sub Demo()
With ActiveSheet.PivotTables(1).RowRange
MsgBox "Last Row: " & .Row + .Rows.Count - 1
End With
End Sub

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Bob Phillips" wrote in message
...
Hi Norm,

I would always use

Range("A65536").End(xlUp).Row

or more specifically

Range("A" & Rows.Count).End(xlUp).Row

as that will always work. UsedRange does not always automatically update.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Norm" wrote in message
...
I'd like opinions on which is more efficient in a macro.

Problem: When using a pivot table, a macro needs to know
the last row. "ActiveSheet.Cells.SpecialCells
(xlCellTypeLastCell).Row" does not get updated when the
pivot table gets filtered. In other words, if the table
starts out at 2000 rows and then is filtered to 10 rows,
the SpecialCells value still says 2000. But after using
UsedRange, the SpecialCells value does get updated.

Which method do you think is more efficient:
1) ActiveSheet.UsedRange.Row +
ActiveSheet.UsedRange.Rows.Count

2) Range("A65536").End(xlUp).Row

Or any other thought on getting the last row of a pivot
table?

Thank You!!