error on excel 2000 when inserting new column

J

jaypee

This message occur-" To prevent possible loss of data, Microsoft Excel
cannot shift nonblank cells off the worksheet. Try to delete or clear the
cells to the right and below your data. Then select cell A1,and save yur
workbook to reset the last cell used or, yo can move the data to a new
location and try again."

please help me about this.
thanks!
 
N

Nick Hodge

Jaypee

Unless you specifically reset excels usedrange occassionally, you can find
that lots of inserting can mean that excel thinks it has data far more to
the right or below of where you think. If you then try to insert say a
column and excel believes you have data in column IV, to stop that data
going 'off the edge' (IV is the last column) you get the error. To reset
the used range, scroll fully to the right and click on the far right column
label. This will select the entire column and then scroll back to the left,
to the last column with actual data and holding down the shift key, click on
this column header too.

You should now have all the columns between end and actual selected. Go to
Edit>Delete and although nothing appears to happen it will have accepted the
input. (NOW SAVE THE WORKBOOK!)

Do the same for rows and you should be fine. Coincedentally you should also
find that your scrollbars now only scroll to the limit of the actual data,
whereas before you started they would scroll way past the end of the data.
(This is the test to see where Excel believes it is)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
(e-mail address removed)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top