What tells Excel where the last cell is (accessed by ctrl-end)?

I have a number of spreadsheets where data has been entered then deleted but
ctrl-end goes to the last cell that ever had data in, not the last cell
currently occupied.

For example, one sheet has no data below row 144 or to the right of column
AF, but ctrl-end takes me to AK170. I've tried deleting entire rows from 145
down to 200 and entire columns from AG to AZ, but still the last used cell
appears to be AK170.

How do I change this?

I suppose one option would be to copy the relevant portions into a new
sheet, but there are a lot of named ranges that I'll have to recreate if I
do this.

--
Ian
--

On a sheet, select all rows below real data range and delete those
rows...........delete.........do not just clear contents.

Same for all columns right of real data range.

NOW..............save the workbook in order to reset the used range.


Gord Dibben MS Excel MVP

On Fri, 30 Jul 2010 20:15:12 +0100, "IanC" <(E-Mail Removed)> wrote:

>What tells Excel where the last cell is (accessed by ctrl-end)?
>
>I have a number of spreadsheets where data has been entered then deleted but
>ctrl-end goes to the last cell that ever had data in, not the last cell
>currently occupied.
>
>For example, one sheet has no data below row 144 or to the right of column
>AF, but ctrl-end takes me to AK170. I've tried deleting entire rows from 145
>down to 200 and entire columns from AG to AZ, but still the last used cell
>appears to be AK170.
>
>How do I change this?
>
>I suppose one option would be to copy the relevant portions into a new
>sheet, but there are a lot of named ranges that I'll have to recreate if I
>do this.
>
>--
>Ian

Ron, I tried your method which was easier to use but in most cases failed,
presumably because of formatting. In those cases I used your method Gord.
Although more time consuming to implement, it worked every time.

Thank you both.

--
Ian
--

"IanC" <(E-Mail Removed)> wrote in message news:4xF4o.12117$bS5.4814@hurricane...
> What tells Excel where the last cell is (accessed by ctrl-end)?
>
> I have a number of spreadsheets where data has been entered then deleted
> but ctrl-end goes to the last cell that ever had data in, not the last
> cell currently occupied.
>
> For example, one sheet has no data below row 144 or to the right of column
> AF, but ctrl-end takes me to AK170. I've tried deleting entire rows from
> 145 down to 200 and entire columns from AG to AZ, but still the last used
> cell appears to be AK170.
>
> How do I change this?
>
> I suppose one option would be to copy the relevant portions into a new
> sheet, but there are a lot of named ranges that I'll have to recreate if I
> do this.
>
> --
> Ian
> --
>
>
>

On 31/07/2010 7:15 a.m., IanC wrote:
> What tells Excel where the last cell is (accessed by ctrl-end)?
>
> I have a number of spreadsheets where data has been entered then deleted but
> ctrl-end goes to the last cell that ever had data in, not the last cell
> currently occupied.
>
> For example, one sheet has no data below row 144 or to the right of column
> AF, but ctrl-end takes me to AK170. I've tried deleting entire rows from 145
> down to 200 and entire columns from AG to AZ, but still the last used cell
> appears to be AK170.
>
> How do I change this?
>
> I suppose one option would be to copy the relevant portions into a new
> sheet, but there are a lot of named ranges that I'll have to recreate if I
> do this.
>
> --
> Ian

You dont have a "set Print Area" on there do you as Ctrl End will send
you to the end of that even if you have deleted the rows.
Lyn

"Lynz" <(E-Mail Removed)> wrote in message
news:i3ngdv$o1a$(E-Mail Removed)...
> On 31/07/2010 7:15 a.m., IanC wrote:
>> What tells Excel where the last cell is (accessed by ctrl-end)?
>>
>> I have a number of spreadsheets where data has been entered then deleted
>> but
>> ctrl-end goes to the last cell that ever had data in, not the last cell
>> currently occupied.
>>
>> For example, one sheet has no data below row 144 or to the right of
>> column
>> AF, but ctrl-end takes me to AK170. I've tried deleting entire rows from
>> 145
>> down to 200 and entire columns from AG to AZ, but still the last used
>> cell
>> appears to be AK170.
>>
>> How do I change this?
>>
>> I suppose one option would be to copy the relevant portions into a new
>> sheet, but there are a lot of named ranges that I'll have to recreate if
>> I
>> do this.
>>
>> --
>> Ian
>
> You dont have a "set Print Area" on there do you as Ctrl End will send
> you to the end of that even if you have deleted the rows.
> Lyn

No, it was down to formatting. In some cases there is a Pring Area, but
Ctrl-End took the cursor beyond this.

--
Ian
--