Last cell

[Guest]

Hi Group,
I am using - ActiveCell.SpecialCells(xlLastCell).Select - to find the lst
cell. Then I am using - Rows((TopRow) & ":" & (BottomRow)).Delete - to delete
a range of rows. What I expected was that the "xlLastCell" would change, but
it did not.

I am essentailly trying to "raise" the location of the "LastCell" to a row
higher than it currectly occupies. In fact I thought this was the only way to
do it? I am using VB 6.3 in Office Professional in Excel 2003. Has something
changed?

 

[John Green]

Hi David,

This is standard Excel behaviour. To reset the LastCell you can either save
the workbook or execute the following:

ActiveSheet.UsedRange

Naturally, if controlling Excel from VB you would need to qualify the above
with a reference to the Excel Application. Something like:

xlApp.ActiveSheet.UsedRange

 

[Guest]

I am not trying to find the used range. I am trying to change the Last used
row from 100 to 80. In previous versions, I believe that I deleted rows 100
to 81 and that raised the last row used to 80. I did not have to save the
workbook and reopen it to do this either.
Thanks

 

[John Green]

Every version of Excel, since it first appeared on the Macintosh in the mid
80s, has exhibited the same behaviour. Deleting data or rows does not reset
the last used row (or the last used column or the used range). Prior to
Excel 97 (if my memory serves me), it was necessary to Save the worksheet or
workbook to reset the last used row/column. It is not necessary to close and
reopen the file. Just Save it.

In the latest versions of Excel the same effect can be achieved with
wks.UsedRange where wks is a reference to the worksheet object.