Hi Group,
I am using version 2003. Prior to this version, when I
deleated rows at the bottom of the used range, it changed
the bottom most used range ie. if last used cell was say
W100 and I delated rows 80 to 100, a new last used range
was created and would be row 80. This does not seem to be
happening in this newer version. This was only allowed if
the range was deleted using code, not simply deleting the
rows in a worksheet with out code. Not exactly new to
this, but has something changed with 2003? Or maybe a new
required perameter or setting?

Thanks

I probably don't understand the problem, but it seems to me
that you need something to detect the delection of rows,
then to call a macro to recalc the last used row. Sounds
like you could make use of one of the Event detectors to do
that.
-----Original Message-----
Hi Group,
I am using version 2003. Prior to this version, when I
deleated rows at the bottom of the used range, it changed
the bottom most used range ie. if last used cell was say
W100 and I delated rows 80 to 100, a new last used range
was created and would be row 80. This does not seem to be
happening in this newer version. This was only allowed if
the range was deleted using code, not simply deleting the
rows in a worksheet with out code. Not exactly new to
this, but has something changed with 2003? Or maybe a new
required perameter or setting?

Thanks
.

Sorry if I was not clear. Will try again. CTRL+End takes
you to the highest used row and column(down the sheet).
The cell does not have to have anything in it, but it did
at one time, during the life of the use of that sheet.
Deleting rows in that worksheet does not change that
Row/Column, if you do CTRL+End you are back to the same
cell. You use to be able to delete that row using code
and the last cell used would change. If you did CTRL +
End, you did not get back to the same row/column, but a
lower row, depending on how many rows you deleted with
the code. In version 2003, I longer seem to be able to do
that and the question is, what has changed or am I
missing something, such as a perameter? Hope this is
clearer. What I am really attempting to do is find the
last cell used and change that last cell, by deleting
rows.

-----Original Message-----
I probably don't understand the problem, but it seems to
me
that you need something to detect the delection of rows,
then to call a macro to recalc the last used row. Sounds
like you could make use of one of the Event detectors to
do
that.
-----Original Message-----
Hi Group,
I am using version 2003. Prior to this version, when I
deleated rows at the bottom of the used range, it
changed
the bottom most used range ie. if last used cell was
say
W100 and I delated rows 80 to 100, a new last used
range
was created and would be row 80. This does not seem to
be
happening in this newer version. This was only allowed
if
the range was deleted using code, not simply deleting
the
rows in a worksheet with out code. Not exactly new to
this, but has something changed with 2003? Or maybe a
new
required perameter or setting?

Thanks
.

.

"David" wrote in message
...

Sorry if I was not clear. Will try again. CTRL+End takes
you to the highest used row and column(down the sheet).
The cell does not have to have anything in it, but it did
at one time, during the life of the use of that sheet.
Deleting rows in that worksheet does not change that
Row/Column, if you do CTRL+End you are back to the same
cell. You use to be able to delete that row using code
and the last cell used would change. If you did CTRL +
End, you did not get back to the same row/column, but a
lower row, depending on how many rows you deleted with
the code. In version 2003, I longer seem to be able to do
that and the question is, what has changed or am I
missing something, such as a perameter? Hope this is
clearer. What I am really attempting to do is find the
last cell used and change that last cell, by deleting
rows.


Try this:

It will unhide all the rows and columns in each sheet (and each sheet)
so you might want to alter that or go back afterward and re-hide as
you
prefer.

Alan.


+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-


Sub ResetUsedRange()


For Each Sht In ActiveWorkbook.Worksheets

Sht.Activate

Sht.Visible = True

Cells.Select


Selection.EntireColumn.Hidden = False

Selection.EntireRow.Hidden = False


Sht.UsedRange

Next


End Sub





+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-

I guess I am not communicating well. Is there a way to
change to last used cell? The cell you get to when you do
Ctrl + End. I don't want to hide anything, but actually
change the last used cell.

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

"David" wrote in
message
...

Sorry if I was not clear. Will try again. CTRL+End
takes
you to the highest used row and column(down the sheet).
The cell does not have to have anything in it, but it
did
at one time, during the life of the use of that sheet.
Deleting rows in that worksheet does not change that
Row/Column, if you do CTRL+End you are back to the same
cell. You use to be able to delete that row using code
and the last cell used would change. If you did CTRL +
End, you did not get back to the same row/column, but a
lower row, depending on how many rows you deleted with
the code. In version 2003, I longer seem to be able to
do
that and the question is, what has changed or am I
missing something, such as a perameter? Hope this is
clearer. What I am really attempting to do is find the
last cell used and change that last cell, by deleting
rows.


Try this:

It will unhide all the rows and columns in each sheet
(and each sheet)
so you might want to alter that or go back afterward and
re-hide as
you
prefer.

Alan.


+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-


Sub ResetUsedRange()


For Each Sht In ActiveWorkbook.Worksheets

Sht.Activate

Sht.Visible = True

Cells.Select


Selection.EntireColumn.Hidden = False

Selection.EntireRow.Hidden = False


Sht.UsedRange

Next


End Sub





+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-



.

"David" wrote in message
...

I guess I am not communicating well. Is there a way to
change to last used cell? The cell you get to when you do
Ctrl + End. I don't want to hide anything, but actually
change the last used cell.


Hi David,

That is what the sub does isn't it? Did you actually run the code?

In fact, it resets the Last Used call for each sheet to the 'optimal'
position
being the least area recognised as being used (minimise file size
etc.)

When you run it on a sheet with the Last Used cell at, say, AA10000
but nothing entered in any cell below or to the right of, say, E50, it
should reset the Last Used cell to E50.

To test that, open a new workbook, go to E50 and enter 'A', and
AA10000 and enter 'A'. Now, go to A1, and press Ctrl-End. You will
go to AA10000. Delete the 'A' from AA10000, go to A1, and press
Ctrl-End. You still go to AA10000.

Now run that code, go to A1, and press Ctrl-End and you will go to
E50.

What happens that you don't want to happen, or conversely, what
doesn't happen that you do want?

Alan.

David,

Not sure what you are trying to do, but CTRL + End does not take you to the
last used cell.
Rather it takes you to the intersection of the last used column and the last
used row.
So if you have something in cell Z5 and something else in A50. CTRL + End
will take you to Z50. To change this - you must delete rows & columns...

--

steveB

(Remove 'NOSPAM' from email address if contacting me direct)


"David" wrote in message
...
Sorry if I was not clear. Will try again. CTRL+End takes
you to the highest used row and column(down the sheet).
The cell does not have to have anything in it, but it did
at one time, during the life of the use of that sheet.
Deleting rows in that worksheet does not change that
Row/Column, if you do CTRL+End you are back to the same
cell. You use to be able to delete that row using code
and the last cell used would change. If you did CTRL +
End, you did not get back to the same row/column, but a
lower row, depending on how many rows you deleted with
the code. In version 2003, I longer seem to be able to do
that and the question is, what has changed or am I
missing something, such as a perameter? Hope this is
clearer. What I am really attempting to do is find the
last cell used and change that last cell, by deleting
rows.

-----Original Message-----
I probably don't understand the problem, but it seems to
me
that you need something to detect the delection of rows,
then to call a macro to recalc the last used row. Sounds
like you could make use of one of the Event detectors to
do
that.
-----Original Message-----
Hi Group,
I am using version 2003. Prior to this version, when I
deleated rows at the bottom of the used range, it
changed
the bottom most used range ie. if last used cell was
say
W100 and I delated rows 80 to 100, a new last used
range
was created and would be row 80. This does not seem to
be
happening in this newer version. This was only allowed
if
the range was deleted using code, not simply deleting
the
rows in a worksheet with out code. Not exactly new to
this, but has something changed with 2003? Or maybe a
new
required perameter or setting?

Thanks
.

.

Sort of have it, but can not change that by deleting the
row. Use to be able to change it with code, but am not
able to do that now in 2003.
But it hink you have the idea.
-----Original Message-----
David,

Not sure what you are trying to do, but CTRL + End does
not take you to the
last used cell.
Rather it takes you to the intersection of the last used
column and the last
used row.
So if you have something in cell Z5 and something else
in A50. CTRL + End
will take you to Z50. To change this - you must delete
rows & columns...

--

steveB

(Remove 'NOSPAM' from email address if contacting me
direct)


"David" wrote in
message
...
Sorry if I was not clear. Will try again. CTRL+End
takes
you to the highest used row and column(down the sheet).
The cell does not have to have anything in it, but it
did
at one time, during the life of the use of that sheet.
Deleting rows in that worksheet does not change that
Row/Column, if you do CTRL+End you are back to the same
cell. You use to be able to delete that row using code
and the last cell used would change. If you did CTRL +
End, you did not get back to the same row/column, but a
lower row, depending on how many rows you deleted with
the code. In version 2003, I longer seem to be able to
do
that and the question is, what has changed or am I
missing something, such as a perameter? Hope this is
clearer. What I am really attempting to do is find the
last cell used and change that last cell, by deleting
rows.

-----Original Message-----
I probably don't understand the problem, but it seems
to
me
that you need something to detect the delection of
rows,
then to call a macro to recalc the last used row.
Sounds
like you could make use of one of the Event detectors
to
do
that.
-----Original Message-----
Hi Group,
I am using version 2003. Prior to this version, when
I
deleated rows at the bottom of the used range, it
changed
the bottom most used range ie. if last used cell was
say
W100 and I delated rows 80 to 100, a new last used
range
was created and would be row 80. This does not seem
to
be
happening in this newer version. This was only
allowed
if
the range was deleted using code, not simply deleting
the
rows in a worksheet with out code. Not exactly new to
this, but has something changed with 2003? Or maybe a
new
required perameter or setting?

Thanks
.

.



.