Why am I not able to insert a row in my worksheet?

[Laurence]

I am trying to insert a row in a worksheet, using the same commands as usual,
and I get this messsage: "To prevent possible loss of data, Excel cannot
shift nonblank cells off the worksheet. Try to locate the last nonblank cell
by pressing control+end, and delete or clear all between the last cell and
the end of 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."
I have no idea what is triggering this message, what it means, or what to
do. I have tried following the recommended commands, and nothing changes. I
am totally baffled. What is going on?

 

[James Booker]

Sounds to me like you have some data in the very last row of the
worksheet (row 65536)

The easiest way to tell is to look at the scrollbars that you use to
move around your worksheet. If the draggable bit is really tiny (which
it shouldn't be unless you have a huge spreadsheet) then you have some
data. When you're adding a new row, it would push this data right off
the end of the sheet - which Excel is trying to tell you it's not
willing to do.

If you can't find anything in the last row (press control + down arrow
to jump there, then start at column A and work across) then the easiest
way is to go to the last row you know you have data in, select all the
rows below it by holding the mouse button on the number of the first
empty row then dragging down, then choosing Edit->Delete.

This should now enable you to add new rows

 

[Laurence]

Hi James:
Thanks for your post. I had already tried clearing the last row, and it
didn't make a difference. I had hoped not to have to scroll all the way down
- it takes a while, and the active part of my worksheet is under 200 rows,
but I grabbed a cup of coffee, and dragged down to the bitter end, then did
edit - delete. Now the insert command works again. I am wondering what caused
this malfunction, because as far as I can see all these rows were empty, they
did not contain any formulas, nothing. The only setting I changed from
default is to have the sheet hide zero values. I wonder if there is an Excel
bug that is creating this problem (it has now happened several times), and if
there is a patch for it.
Anyway, thanks, you just saved me $35. Have a great day.

 

[Tom]

Ctrl-end - will take you quickly to the bottom right - touched area of a
sheet...