SpecialCells and UsedRange

[Norm]

I am using a pivot table that starts on row 5. A macro
needs to know the bottom row of the pivot table, so I used
ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row.

I'm finding a problem when we filter the pivot table - say
from 2000 rows to 10 rows - the SpecialCells variable
still gives 2000 rows. But if I access UsedRange, then
SpecialCells gets updated to the correct value of 10.

Any ideas why SpecialCells(xlCellTypeLastCell) does not
update correctly?

Thanks!!

 

[Charles Williams]

Hi Norm,

Excel's used range reflects the last cell that has been used since it was
last reset. Used includes formatting etc.

Probably better to use something like range("a65536").end(xlup) to find
the last nonblank cell in column A.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Tom Ogilvy]

activesheet.UsedRange

will usually reset it. (tell excel to reevaluate what cells it needs to keep
track of) (and as you have apparently discovered)

It doesn't update until you take an action to update it. UsedRange works
exactly as it should. It just doesn't match your expectations. Except for
the bug that existed in the original release of xl2000, it acurately
reflects the extent of the cells Excel is maintaining information on.
That is the definition of usedrange - not the size of your pivottable.