Insert Row/Column Excel 2007

[Tyro]

I'm playing in a new workbook, which has not been saved. On the first 3
worksheets, I'm just playing with simple formulas. I insert a new worksheet.
With A1 selected I attempt to insert a new column and get the message:
Cannot shift objects off sheet. I get the same message if I try to insert a
row. The sheet is new, no entries or objects on it. I open a new workbook
and have no problem inserting rows or columns on a blank worksheet. Any
ideas?

Tyro

 

[Nick Hodge]

Tyro

Whilst you have loads of new rows and columns in excel 2007. I suspect this
workbook is still in 'compatibility mode'.

The root problem is that Excel believes that there is data in row 65536
and/or column IV, so that inserting rows or columns would move data off the
'old' grid size.

To stop this issue, highlight A1 and press Ctrl+End. Note where your cursor
ends up (we are presuming Z65000 for this demo, so adjust to where you end
up). now you look to see where your 'actual' data finishes. (again, we will
assume G256 for this demo).

Select rows 65000 to 256 (that is entire rows, not just the cells) and
right-click a row number that is selected and select 'delete'. Not
'clear'...DELETE. Now select columns H to Z and do the same for columns as
you did for rows. Remember DELETE, not clear. Now save the workbook. (Very
important).

Now when you press Ctrl+End you should end up at G256 in our example and the
problem will have gone. (Equally, it should go if you save the xls file as a
xlsx (new format file) and close and re-open it as a 2007 file)

Let me know if this works as I have seen the issue in <2007, so am making a
presumption in 2007.

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

 

[Tyro]

I select A1. I press Ctrl+End and the cursor stays in A1. The entire sheet
of 1048576 rows and 16384 columns is empty.

Tyro

 

[Nick Hodge]

Tyro

Is this an old workbook? If so does it do the same with a new workbook? If
it is a new workbook, try temporarily removing any of the following files
from their present location (Note where they are before moving them and set
the search to look for hidden folders and files as some may be)

Personal.xls
Book.xlt
Sheet.xlt
Excel[x].xlb

Where [x] is the version number, e.g. 10, 11, etc.

Anything in the xlStart folder.

if this fails we are onto add-ins, but post back

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

 

[Dave Peterson]

Maybe...

XL: "Cannot Shift Objects Off Sheet" Error Hiding Columns
http://support.microsoft.com/default.aspx?scid=kb;en-ca;211769

Remember to look for comments and merged cells.

==========
If you open excel in safe mode:

Close excel
windows start button|Run
excel /safe

Do you have the same problem?

Maybe there's something in the sheet.xlt* template file that's causing trouble.

 

[Tyro]

This is a brand new work book. I created it in Excel 2007, played with some
formulas on sheets 1, 2 and 3. Then I inserted a new worksheet and tried to
insert a row and a column on it and got the message "Cannot shift objects
off sheet.". There are no objects - i.e. drawings, pictures etc on the
sheet. I get the same message if I try the insert on the original 3 sheets
on the workbook. This is not the usual message one gets if there is
something in the last column or row and one tries to insert a new column or
row. That message is: "To prevent possible loss of data, Excel cannot shift
cells off of the worksheet. Select another location in which to insert new
cells, or delete data from the end of your worksheet.". I leave the current
workbook with the problem open and create another new workbook and have no
problem adding rows or columns to empty sheets there. Both workbooks have
1048576 rows and 16384 columns. I've been using Excel 2007 for about 9
months. This is the first time I've seen this problem.

Tyro

Tyro

Is this an old workbook? If so does it do the same with a new workbook?
If it is a new workbook, try temporarily removing any of the following
files from their present location (Note where they are before moving them
and set the search to look for hidden folders and files as some may be)

Personal.xls
Book.xlt
Sheet.xlt
Excel[x].xlb

Where [x] is the version number, e.g. 10, 11, etc.

Anything in the xlStart folder.

if this fails we are onto add-ins, but post back

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

I select A1. I press Ctrl+End and the cursor stays in A1. The entire
sheet of 1048576 rows and 16384 columns is empty.

Tyro

 

[Nick Hodge]

Tyro

And the files?

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

This is a brand new work book. I created it in Excel 2007, played with
some formulas on sheets 1, 2 and 3. Then I inserted a new worksheet and
tried to insert a row and a column on it and got the message "Cannot shift
objects off sheet.". There are no objects - i.e. drawings, pictures etc on
the sheet. I get the same message if I try the insert on the original 3
sheets on the workbook. This is not the usual message one gets if there is
something in the last column or row and one tries to insert a new column
or row. That message is: "To prevent possible loss of data, Excel cannot
shift cells off of the worksheet. Select another location in which to
insert new cells, or delete data from the end of your worksheet.". I
leave the current workbook with the problem open and create another new
workbook and have no problem adding rows or columns to empty sheets there.
Both workbooks have 1048576 rows and 16384 columns. I've been using Excel
2007 for about 9 months. This is the first time I've seen this problem.

Tyro

Tyro

Is this an old workbook? If so does it do the same with a new workbook?
If it is a new workbook, try temporarily removing any of the following
files from their present location (Note where they are before moving them
and set the search to look for hidden folders and files as some may be)

Personal.xls
Book.xlt
Sheet.xlt
Excel[x].xlb

Where [x] is the version number, e.g. 10, 11, etc.

Anything in the xlStart folder.

if this fails we are onto add-ins, but post back

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

I select A1. I press Ctrl+End and the cursor stays in A1. The entire
sheet of 1048576 rows and 16384 columns is empty.

Tyro

Tyro

Whilst you have loads of new rows and columns in excel 2007. I suspect
this workbook is still in 'compatibility mode'.

The root problem is that Excel believes that there is data in row 65536
and/or column IV, so that inserting rows or columns would move data off
the 'old' grid size.

To stop this issue, highlight A1 and press Ctrl+End. Note where your
cursor ends up (we are presuming Z65000 for this demo, so adjust to
where you end up). now you look to see where your 'actual' data
finishes. (again, we will assume G256 for this demo).

Select rows 65000 to 256 (that is entire rows, not just the cells) and
right-click a row number that is selected and select 'delete'. Not
'clear'...DELETE. Now select columns H to Z and do the same for columns
as you did for rows. Remember DELETE, not clear. Now save the workbook.
(Very important).

Now when you press Ctrl+End you should end up at G256 in our example
and the problem will have gone. (Equally, it should go if you save the
xls file as a xlsx (new format file) and close and re-open it as a 2007
file)

Let me know if this works as I have seen the issue in <2007, so am
making a presumption in 2007.

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





I'm playing in a new workbook, which has not been saved. On the first
3 worksheets, I'm just playing with simple formulas. I insert a new
worksheet. With A1 selected I attempt to insert a new column and get
the message: Cannot shift objects off sheet. I get the same message if
I try to insert a row. The sheet is new, no entries or objects on it.
I open a new workbook and have no problem inserting rows or columns on
a blank worksheet. Any ideas?

Tyro

 

[Tyro]

This is a brand new workbook. I played with some simple formulas on the
first 3 sheets in A1:F10. Then I inserted a new sheet (Sheet 4) which has
nothing on it. I just inserted the sheet and immediately tried inserting a
column and got the message. I opened another new workbook, inserted a sheet
(Sheet4) and did not have the problem.
In the workbook with the problem, I cannot insert rows or columns on the
original 3 sheets either. Both workbooks have 1048576 rows and 16384
columns.
See my post to Nick above.

Tyro

 

[Tyro]

I don't have .xls files or xlt. files. I use only Excel 2007 files and have
no book or sheet templates. I have a personal file but all it has in it is a
loan amortization sheet.

Tyro

Tyro

And the files?

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

This is a brand new work book. I created it in Excel 2007, played with
some formulas on sheets 1, 2 and 3. Then I inserted a new worksheet and
tried to insert a row and a column on it and got the message "Cannot
shift objects off sheet.". There are no objects - i.e. drawings, pictures
etc on the sheet. I get the same message if I try the insert on the
original 3 sheets on the workbook. This is not the usual message one gets
if there is something in the last column or row and one tries to insert a
new column or row. That message is: "To prevent possible loss of data,
Excel cannot shift cells off of the worksheet. Select another location in
which to insert new cells, or delete data from the end of your
worksheet.". I leave the current workbook with the problem open and
create another new workbook and have no problem adding rows or columns to
empty sheets there. Both workbooks have 1048576 rows and 16384 columns.
I've been using Excel 2007 for about 9 months. This is the first time
I've seen this problem.

Tyro

Tyro

Is this an old workbook? If so does it do the same with a new workbook?
If it is a new workbook, try temporarily removing any of the following
files from their present location (Note where they are before moving
them and set the search to look for hidden folders and files as some may
be)

Personal.xls
Book.xlt
Sheet.xlt
Excel[x].xlb

Where [x] is the version number, e.g. 10, 11, etc.

Anything in the xlStart folder.

if this fails we are onto add-ins, but post back

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





I select A1. I press Ctrl+End and the cursor stays in A1. The entire
sheet of 1048576 rows and 16384 columns is empty.

Tyro

Tyro

Whilst you have loads of new rows and columns in excel 2007. I suspect
this workbook is still in 'compatibility mode'.

The root problem is that Excel believes that there is data in row
65536 and/or column IV, so that inserting rows or columns would move
data off the 'old' grid size.

To stop this issue, highlight A1 and press Ctrl+End. Note where your
cursor ends up (we are presuming Z65000 for this demo, so adjust to
where you end up). now you look to see where your 'actual' data
finishes. (again, we will assume G256 for this demo).

Select rows 65000 to 256 (that is entire rows, not just the cells) and
right-click a row number that is selected and select 'delete'. Not
'clear'...DELETE. Now select columns H to Z and do the same for
columns as you did for rows. Remember DELETE, not clear. Now save the
workbook. (Very important).

Now when you press Ctrl+End you should end up at G256 in our example
and the problem will have gone. (Equally, it should go if you save the
xls file as a xlsx (new format file) and close and re-open it as a
2007 file)

Let me know if this works as I have seen the issue in <2007, so am
making a presumption in 2007.

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





I'm playing in a new workbook, which has not been saved. On the first
3 worksheets, I'm just playing with simple formulas. I insert a new
worksheet. With A1 selected I attempt to insert a new column and get
the message: Cannot shift objects off sheet. I get the same message
if I try to insert a row. The sheet is new, no entries or objects on
it. I open a new workbook and have no problem inserting rows or
columns on a blank worksheet. Any ideas?

Tyro

 

[Dave Peterson]

How about posting your results when you opened excel in safe mode?

 

[Tyro]

The problem occurs only in this one workbook. Other workbooks I create work
fine.

 

[Dave Peterson]

What happened when you tried the things in the KB article?

The problem occurs only in this one workbook. Other workbooks I create work
fine.

 

[Tyro]

The workbook was created by Excel 2007. I reduced the workbook down to one
sheet. I inserted a new sheet, Sheet 2. I deleted sheet 1. I hid several
columns on sheet 2. I got no message. I tried to insert a column and got the
message "Cannot shift objects off sheet with code 100185 as described in the
KB. My inserted sheet is new and empty. No comments. I select A1 and press
Ctrl+End and A1 stays selected. I opened a new workbook and added all the
rows and columns I wanted. I uploaded the problem workbook to savefile.com
http://www.savefile.com/files/1342535 if you want to play with it.

Tyro

 

[Dave Peterson]

I don't know.

I haven't used xl2007 enough to know a lot about its features.

But I opened your workbook in xl2007 and saved it as a .xls (xl2003 version) and
reopened that version in xl2003. I could insert a new column without a problem.

I saved the file (with that single change) and reopened the .xls file in xl2007
in compatibility mode. I couldn't insert a column.

It seems like a bug to me, but that's coming from someone who hasn't used xl2007
enough to really guess.

The workbook was created by Excel 2007. I reduced the workbook down to one
sheet. I inserted a new sheet, Sheet 2. I deleted sheet 1. I hid several
columns on sheet 2. I got no message. I tried to insert a column and got the
message "Cannot shift objects off sheet with code 100185 as described in the
KB. My inserted sheet is new and empty. No comments. I select A1 and press
Ctrl+End and A1 stays selected. I opened a new workbook and added all the
rows and columns I wanted. I uploaded the problem workbook to savefile.com
http://www.savefile.com/files/1342535 if you want to play with it.

Tyro

 

[Tyro]

I've been playing with Excel 2007 for 9 months. I've never seen this before.

 

[Dave Peterson]

Maybe someone else can try your troublesome file.

I've been playing with Excel 2007 for 9 months. I've never seen this before.

 

[Roger Govier]

Hi Tyro

I downloaded your workbook and confirm the same problems.
Inserting another sheet in that workbook, gives the same problem.
Moving the sheet to another workbook, removes the problem hence it is not
Sheet specific, just Workbook specific.
I have examined all properties of the Workbook, and cannot see anything that
would cause this behaviour.
I guess, somehow a flag got set somewhere internally, that causes this
behaviour.

I also have been using 2007 for about 9 months, and I have never come across
this problem.
Sorry I can't be of more help.

--

Regards
Roger Govier

I've been playing with Excel 2007 for 9 months. I've never seen this
before.

 

[Tyro]

Well, stranger things have happened. It's just a little odd. I create new
workbooks all the time to play with things.

Tyro

Hi Tyro

I downloaded your workbook and confirm the same problems.
Inserting another sheet in that workbook, gives the same problem.
Moving the sheet to another workbook, removes the problem hence it is not
Sheet specific, just Workbook specific.
I have examined all properties of the Workbook, and cannot see anything
that would cause this behaviour.
I guess, somehow a flag got set somewhere internally, that causes this
behaviour.

I also have been using 2007 for about 9 months, and I have never come
across this problem.
Sorry I can't be of more help.