Lock out of insert row or column

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.....
 >> Stay informed about: Lock out of insert row or column 

In article <ee8a085.2.TakeThisOut@webcrossing.caR9absDaxw>,
Guitar_player_575.TakeThisOut@officeformac.com wrote:

> 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.
 >> Stay informed about: Lock out of insert row or column 

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.

> On 1/22/08 8:14 PM, in article ee8a085.-1.RemoveThis@webcrossing.caR9absDaxw, "DRColby@officeformac.com" <DRColby.RemoveThis@officeformac.com> wrote:
>
> 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.
>
> --
> Bob Greenblatt [MVP], Macintosh
> bobgreenblattATmsnDOTcom
>
>
 >> Stay informed about: Lock out of insert row or column 

Hi Richard -

Some points below:


On 4/26/08 3:15 PM, in article ee8a085.4.TakeThisOut@webcrossing.caR9absDaxw, "Richard
Johnson" <johnra0a.TakeThisOut@comcast.net> wrote:

> 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
 >> Stay informed about: Lock out of insert row or column