Last cell
 

Hi Group,
This is a second post for this question and I am having a
hard time explaining it, but here it goes again.

Ctrl+End takes you to the last used cell in a worksheet.
I am using version 2003. In previous versions, I could
change the last used cell by deleting rows, but only by
doing this with code. I don't seem to be able to do that
anymore with this version. Has something changed or maybe
I am missing a parameter?

Thanks

Last cell
 

since xl97(SR-1?).. the last cell is reset on save.

the last cell (as microsoft sees it) is the last cell which contains
data, comments or FORMATS(fill/numberformat or else).

you can force a recompute of lastcell:
in code: by calling the usedrange method.
but you may have to clear formats outside "your" usedrange first.

Dim lng&
lng = wks.UsedRange.Count

Manually by DELETING all Rows and Columns beyond
what you want the usedrange to be.

HOWEVER: if row 65536 or column 256 or any cell therein
contains any formatting.. you FIRST have to reset the
formats else the newly inserted rows will get that format and your
usedrange wont change.




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"David" wrote:

Hi Group,
This is a second post for this question and I am having a
hard time explaining it, but here it goes again.

Ctrl+End takes you to the last used cell in a worksheet.
I am using version 2003. In previous versions, I could
change the last used cell by deleting rows, but only by
doing this with code. I don't seem to be able to do that
anymore with this version. Has something changed or maybe
I am missing a parameter?

Thanks

Last cell
 

David,

What is it, exactly, that you "don't seem able to do"? Delete
rows with code?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"David" wrote in message
...
Hi Group,
This is a second post for this question and I am having a
hard time explaining it, but here it goes again.

Ctrl+End takes you to the last used cell in a worksheet.
I am using version 2003. In previous versions, I could
change the last used cell by deleting rows, but only by
doing this with code. I don't seem to be able to do that
anymore with this version. Has something changed or maybe
I am missing a parameter?

Thanks

Last cell
 

One more try. I have a worksheet, where I am putting a
table that is 1000 rows "tall" and it is below some
existing data, which is some 300 rows "tall." Once this
is done, the table is then deleted. If you do a Ctrl +
End, you end up somewhere around row 1300, which is way
below the data. If I recreate the table, the code finds
the last used cell ie row 1300 and puts the table there.
In previous versions I could change that last used cell
by deleting rows with code, but I am not able to do that
anymore. I can NOT change the last used cell. Does anyone
know how I can change the the last used cell?

The last used cell does not have to contain data or
comments. As I mentioned, I had been able to delete rows
in previous versions and permanently change the last used
cell ie if I deleted rows 1000 to 1300, where 1300 had
been the last used row, a new last used cell was created
and became row 1000. It also did not matter that some of
these cells contained data.

Thanks for your help.

-----Original Message-----
Hi Group,
This is a second post for this question and I am having
a
hard time explaining it, but here it goes again.

Ctrl+End takes you to the last used cell in a worksheet.
I am using version 2003. In previous versions, I could
change the last used cell by deleting rows, but only by
doing this with code. I don't seem to be able to do that
anymore with this version. Has something changed or
maybe
I am missing a parameter?

Thanks
.

Last cell
 

Hi Cool,
I am not quite sure if I explained myself well enough. I
am trying to change the last used cell from cell 1300 to
cell 300. If I press Crtl + End, where the last used cell
is 1300, is it possible to change what happens when I
press Ctrl + End, so that it does not end up at row 1300?

Thanks for your help.

-----Original Message-----

since xl97(SR-1?).. the last cell is reset on save.

the last cell (as microsoft sees it) is the last cell
which contains
data, comments or FORMATS(fill/numberformat or else).

you can force a recompute of lastcell:
in code: by calling the usedrange method.
but you may have to clear formats outside "your"
usedrange first.

Dim lng&
lng = wks.UsedRange.Count

Manually by DELETING all Rows and Columns beyond
what you want the usedrange to be.

HOWEVER: if row 65536 or column 256 or any cell therein
contains any formatting.. you FIRST have to reset the
formats else the newly inserted rows will get that
format and your
usedrange wont change.




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"David" wrote:

Hi Group,
This is a second post for this question and I am
having a
hard time explaining it, but here it goes again.

Ctrl+End takes you to the last used cell in a
worksheet.
I am using version 2003. In previous versions, I could
change the last used cell by deleting rows, but only
by
doing this with code. I don't seem to be able to do
that
anymore with this version. Has something changed or
maybe
I am missing a parameter?

Thanks


.

Last cell
 

Hi David,
To positively make a cell the last cell in the worksheet
http://www.mvps.org/dmcritchie/excel...m#makelastcell

To run through the entire workbook to fix last cell problems,
this will usually work. It does not delete anything, but it does not
test to see if it worked -- it is real fast.
http://www.mvps.org/dmcritchie/excel...l.htm#resetall

Neither of the above will save the workbook, so change will
not be seen if you exit without saving. Difference between
what you do manually and what you can do in a macro.

If you manually delete rows and columns to effect change to the
lastcell indication, then you have to save the file.

I have Excel 2000. What version do you have.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"David" wrote in message ...
Hi Cool,
I am not quite sure if I explained myself well enough. I
am trying to change the last used cell from cell 1300 to
cell 300. If I press Crtl + End, where the last used cell
is 1300, is it possible to change what happens when I
press Ctrl + End, so that it does not end up at row 1300?

Thanks for your help.

-----Original Message-----

since xl97(SR-1?).. the last cell is reset on save.

the last cell (as microsoft sees it) is the last cell
which contains
data, comments or FORMATS(fill/numberformat or else).

you can force a recompute of lastcell:
in code: by calling the usedrange method.
but you may have to clear formats outside "your"
usedrange first.

Dim lng&
lng = wks.UsedRange.Count

Manually by DELETING all Rows and Columns beyond
what you want the usedrange to be.

HOWEVER: if row 65536 or column 256 or any cell therein
contains any formatting.. you FIRST have to reset the
formats else the newly inserted rows will get that
format and your
usedrange wont change.




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"David" wrote:

Hi Group,
This is a second post for this question and I am
having a
hard time explaining it, but here it goes again.

Ctrl+End takes you to the last used cell in a
worksheet.
I am using version 2003. In previous versions, I could
change the last used cell by deleting rows, but only
by
doing this with code. I don't seem to be able to do
that
anymore with this version. Has something changed or
maybe
I am missing a parameter?

Thanks


.

Last cell
 

Should have noted that you might want to check
File, Properties, size before and after.
Though if you close without saving then no change
will have actually taken place