When I try and insert a row I get a prompt saying

"To prevent possible loss of data, Excel cannot shift non-blank cells
off the worksheet. Try to locate the last non-blank cell by pressing
CTRL+END and delete or clear all cells in between the last cell and the
end of your data. Then select cell A1 and save your workbook to reset
the last cell used."

Can anyone advise (a) Why this happens and (b) How to stop it happening
as I've tried doing what the prompt says and can't seem to solve
it...and I really need to insert a row, rather than cut & paste.

Any help greatly appreciated....Thanks

Excel 2003 has a 65K row capacity and in Excel 2007 they pushed it up to 1M
rows.

This error means you insert rows, existing rows will have to be pushed off
the maximum limit. This is done to prevent you from losing rows by mistake.

Elad
http://www.sisense.com
"Never redefine Excel ranges again!"

"Eliot" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> When I try and insert a row I get a prompt saying
>
> "To prevent possible loss of data, Excel cannot shift non-blank cells off
> the worksheet. Try to locate the last non-blank cell by pressing CTRL+END
> and delete or clear all cells in between the last cell and the end of your
> data. Then select cell A1 and save your workbook to reset the last cell
> used."
>
> Can anyone advise (a) Why this happens and (b) How to stop it happening as
> I've tried doing what the prompt says and can't seem to solve it...and I
> really need to insert a row, rather than cut & paste.
>
> Any help greatly appreciated....Thanks

I understand that but the affected sheet only has 1,500 rows of data?

Why would it do it in these circumstances?


Elad wrote:
> Excel 2003 has a 65K row capacity and in Excel 2007 they pushed it up to 1M
> rows.
>
> This error means you insert rows, existing rows will have to be pushed off
> the maximum limit. This is done to prevent you from losing rows by mistake.
>
> Elad
> http://www.sisense.com
> "Never redefine Excel ranges again!"
>
> "Eliot" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> When I try and insert a row I get a prompt saying
>>
>> "To prevent possible loss of data, Excel cannot shift non-blank cells off
>> the worksheet. Try to locate the last non-blank cell by pressing CTRL+END
>> and delete or clear all cells in between the last cell and the end of your
>> data. Then select cell A1 and save your workbook to reset the last cell
>> used."
>>
>> Can anyone advise (a) Why this happens and (b) How to stop it happening as
>> I've tried doing what the prompt says and can't seem to solve it...and I
>> really need to insert a row, rather than cut & paste.
>>
>> Any help greatly appreciated....Thanks
>
>

Excel has a habit of overestimating the real used range on a worksheet.

If you have used 4000 rows then clear the contents of row 1000 to 4000, Excel
still thinks you are using 4000 rows.

Go to row 1501 and select it.

SHIFT + End + DownArrow.

Edit>Delete>Entire Row.

Do same for columns to the right of your data range.

Now.....IMPORTANT.....Save the workbook.

I find if you save then close then re-open, the used range will be reset every
time.


Gord Dibben MS Excel MVP


On Wed, 27 Feb 2008 00:49:19 +0000, Eliot <(E-Mail Removed)> wrote:

>I understand that but the affected sheet only has 1,500 rows of data?
>
>Why would it do it in these circumstances?
>
>
>Elad wrote:
>> Excel 2003 has a 65K row capacity and in Excel 2007 they pushed it up to 1M
>> rows.
>>
>> This error means you insert rows, existing rows will have to be pushed off
>> the maximum limit. This is done to prevent you from losing rows by mistake.
>>
>> Elad
>> http://www.sisense.com
>> "Never redefine Excel ranges again!"
>>
>> "Eliot" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> When I try and insert a row I get a prompt saying
>>>
>>> "To prevent possible loss of data, Excel cannot shift non-blank cells off
>>> the worksheet. Try to locate the last non-blank cell by pressing CTRL+END
>>> and delete or clear all cells in between the last cell and the end of your
>>> data. Then select cell A1 and save your workbook to reset the last cell
>>> used."
>>>
>>> Can anyone advise (a) Why this happens and (b) How to stop it happening as
>>> I've tried doing what the prompt says and can't seem to solve it...and I
>>> really need to insert a row, rather than cut & paste.
>>>
>>> Any help greatly appreciated....Thanks
>>
>>

Would just like to comment that the above solution worked for me - I
hadn't bothered to delete columns to the right of my data.

Thanks!

Alex Pepper

Yes, I had to save it and re-open for it to work.

Thanks!