ActiveSheet.UsedRange.Rows.Count

Hi

I'm using ActiveSheet.UsedRange.Rows.Count to identify the last row in a Pivot Table but when pivot table is updated and number of rows is less than the previous version it still counts the rows as if it's the old Pivot Table defining the area. Funny part is it only does it in the first sheet. All the following sheets is correctly updated. Any way to get around this?

Thanks,
Kjaer

Some details:

Old Pivot Table is 59 rows
New Pivot Table is 46 rows

I'm deleting the data not part of the Pivot Table using this code (PT has been updated at this point of time):

Range("I13:L65536").Select
Selection.Delete
Selection.Interior.ColorIndex = xlNone

Then I'm trying to identify the number of rows with this:

Dim myCount As Integer
myCount = ActiveSheet.UsedRange.Rows.Count

But when I copy the formulas I need and try to paste them next to the PT using this code:

Range("I12:L12").Select
Selection.Copy
Range("I13:L" & myCount).Select
ActiveSheet.Paste

it still assumes 59 rows

I'm not sure if I totally understand what you're trying to do. But if you're trying to update pivot tables and remove missing items, then this should help:

Dim PivotCacheWork As PivotCache

'Refresh Pivot Tables

For Each PivotCacheWork In ThisWorkbook.PivotCaches
PivotCacheWork.Refresh
Next PivotCacheWork

'Remove Missing Items

ActiveSheet.PivotTables(1).PivotCache.MissingItemsLimit = xlMissingItemsNone

Try pasting this code somewhere in the beginning of your code.

HTH,
Seth

Thanks for the input. Sorry if my question was a bit unclear. Basically what I'm looking for is a way to identify the last row used in a specific sheet (containing a Pivot Table and some manual formulas next to the PT).

As I'm not using Grand Total in all of the sheets, I can't use "match" to identify it. Thus I was trying to use the "ActiveSheet.UsedRange.Rows.Count" instead. Unfortunately this causes some problems when the updated PT contains less rows than the previous one (the macro have deleted the manually added formulas before I use the "ActiveSheet.UsedRange.Rows.Count") as the "ActiveSheet.UsedRange.Rows.Count" identifies the last row as the one matching the previous PT and not the new one. It seems that Excel somehow doesn't update the "last cell" when cells are cleared/deleted. Maybe it takes something specific to "provoce" Excel to update it?

The funny part is that it's only a problem in the first sheet. In the rest of the sheets it works fine (also in cases where the new PT is less rows than the previous one) - even though I've basically did a copy/paste so the coding is basically the same for those sheets as the first sheet.

Hope it makes more sense this time :-).