Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells and UsedRange
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!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells and UsedRange
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 "Norm" wrote in message ... 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!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SpecialCells and UsedRange
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. -- Regards, Tom Ogilvy "Norm" wrote in message ... 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!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Specialcells | Charts and Charting in Excel | |||
SpecialCells(xlCellTypeFormulas) | Excel Programming | |||
AutoFilter /specialcells | Excel Programming | |||
specialcells(xlcelltypeblanks) | Excel Programming | |||
Usedrange | Excel Programming |