Excel error - inserting columns/rows

[JayE]

Hi,

My colleague and I both have the same problem with a few Excel 2000 spreadsheets.

When my colleague or I try to insert a column or row, we get this message:

To prevent possible loss of data, Microsoft Excel cannot shift nonblank cells off the worksheet.

Try to delete or clear cells to the right and below your data. Then select cell A1, and save your workbook to reset the last cell used.

Or, you can move the data to a new location and try again.

We can't understand what this is all about, especially when inserting rows. We've never had this message before.

Can you explain what Excel is complaining about and how to resolve it?

Regards,
Jay/UK

 

[xlbo]

Sounds like you have data or formatting in cells near the end of your spreadsheet (column IV or row 65536)

Excel has a limit of 256 columns and 65536 rows

If you insert rows, what you are doing is shifting all rows below them downwards. The last x rows (where x is the number of rows you are inserting) get shifted off the spreadsheet altogether. If excel thinks that you have data in rows that will be shifted off the spreadhseet via this method, it'll give the error you have encountered

HTh Rgds
~Geoff~

 

[JayE]

Hi Geoff,

Thanks very much.

I can see how this would produce this error message.

And I know that occasionally I find myself accidentally entering text in Row 65536 - and realised I must've pressed the wrong key (not sure which one!).

Is there a quick way of jumping to the last row (or column) with data in?

Regards,
Jay/UK

 

[xlbo]

Use CTRL + Arrow keys to jump to the end of data (you can also use End and then an arrow key)

or, and this may be applicable in your case, use
CTRL+G
then select special
then select last cell

This should take you to what excel thinks is the last cell with data/formatting in it

HTH Rgds
~Geoff~

 

[JayE]

Hi,

Tried this, with Control G and it jumps to cell T65536, but there's no data or formula there. When I try to delete it (in case it's invisible!) it makes no difference to my inability to insert rows (I can insert columns in this particular spreadsheet).

Any further suggestions?

Regards,
Jay/UK

 

[xlbo]

Try clearing the formats from the cell
Go to Format, cells
check whether a special number format has been set / alignment / borders etc - also whether a colored font / fill has been used. All of the above will result in your error message Rgds
~Geoff~

 

[iGrant]

You can use Ctrl + End to have Excel go to the cell that it thinks is the bottom right hand corner of the spreadsheet.

If you have selected a single row and 'merged' it by mistake, this will prevent the insertion of columns (because it will have affected the cell in column IV, as Geoff suggests above).