Can't insert Rows?

[Eliot]

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

 

[Elad]

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]

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

Why would it do it in these circumstances?

 

[Gord Dibben]

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

 

[alex.pepper1984]

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

 

[gabriel]

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

Thanks!

 

[rajkumartuty]

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

Thanks for the info...its working out for me