Lock out of insert row or column

[DRColby]

When trying to insert row or column in sheet from Excel '04 I am locked out with the following message: "cannot shift objects off sheet"
Any thoughts? Sheet is not protected.

Dave

 

[Bob Greenblatt]

When trying to insert row or column in sheet from Excel '04 I am locked out
with the following message: "cannot shift objects off sheet"
Any thoughts? Sheet is not protected.

Dave

You get this error because there is something (like maybe a single space
character) in a cell that would get pushed off the right or below the bottom
of the sheet with the insert. To try to find this press control-end and see
where the active cell ends up. Then DELETE all the columns and/or ROWS to
the right and below the cell where YOU think the end of the worksheet should
be. Then SAVE and CLOSE the workbook. When It is re opened, it should be OK.

 

[flam]

I have the exact same problem, but the answer did not solve it. What else can I do?

Frances

 

[Guitar_player_575]

I am stuck, too - when I go to Control-end, I end up in Column I Row 1048576. My data runs from A1 to AM99. All I want to do is insert just two rows at Row 16. It should be so simple.....

 

[JE McGimpsey]

I am stuck, too - when I go to Control-end, I end up in Column I Row 1048576.
My data runs from A1 to AM99. All I want to do is insert just two rows at Row
16. It should be so simple.....

Try typing

100:1048576

into the name box (on the left side of the formula bar), then press
Return. This will select all the non-used rows. Choose Edit/Delete Row
(you probably won't see a change - XL just creates new rows). Save,
close, and reopen the workbook. CTRL-end should now take you to AM99.

 

[Richard Johnson]

When I copy an entire existing row and attempt to insert it between to other existing rows in a large spreadsheet, I receive the same error message 'Cannot shift objects off sheet'.

The Control-End then Delete advice did not solve the problem. The error message would seem to indicate that Excel 2008 now arbitrarily default limits the spreadsheet as to number of rows and/or columns allowed. Might this have to do with the number of sheets selected when initially setting up the workbook?

Need to determine solution, as the alternative Copy & Paste command overwrites essential data.

 

[CyberTaz]

Hi Richard -

Some points below:

The Control-End then Delete advice did not solve the problem.

The recommendation meant that you have to *select* the rows below the last
one with content, then delete the rows in order to have Excel recreate them.
Or at least delete the ones at the edge of the sheet - you have to have as
many rows "clean" on the edge of the sheet as what you are trying to insert.

The error
message would seem to indicate that Excel 2008 now arbitrarily default limits
the spreadsheet as to number of rows and/or columns allowed.

I'm not clear on your point - Excel *does* have a fixed number of columns &
rows. There's nothing arbitrary about it. In the 2004 version it is 256 x
65536. "Inserting" columns or rows or cells does not change that number - it
simply shifts empty cells from the sheet's periphery to the location in
which you're inserting them. IOW, if you have content in all 65536 rows you
can't insert a row. You have to have as many rows/columns at the edge of a
sheet as you are trying to insert.

Might this have
to do with the number of sheets selected when initially setting up the
workbook?

As indicated above, the number of sheets nor anything else changes it... The
sheets are 256 columns wide by 65536 rows deep = 16,777,216 cells per sheet.
If you need more you'll have to upgrade to Excel 2008 - 16,385 columns by
1,048,576 rows.

Regards |:>)
Bob Jones
[MVP] Office:Mac