 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2008Insert Row/Column Excel 2007
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 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.  SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS 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 [quoted text clipped - 5 lines] > > 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 > Tyro > [quoted text clipped - 32 lines] >> >> 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 SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS 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. [quoted text clipped - 37 lines] >>> >>> 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 > [quoted text clipped - 56 lines] >>>> >>>> Tyro Tyro And the files? SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS 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 [quoted text clipped - 73 lines] >>>>> >>>>> 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 > [quoted text clipped - 79 lines] >>>>>> >>>>>> Tyro 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. > 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. [quoted text clipped - 5 lines] > > Tyro SignatureDave Peterson 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 > Maybe... > [quoted text clipped - 26 lines] >> >> Tyro How about posting your results when you opened excel in safe mode? > 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 [quoted text clipped - 42 lines] > > > > Dave Peterson SignatureDave Peterson The problem occurs only in this one workbook. Other workbooks I create work fine. > How about posting your results when you opened excel in safe mode? > [quoted text clipped - 51 lines] >> > >> > 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. [quoted text clipped - 58 lines] > > > > Dave Peterson SignatureDave Peterson 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 > What happened when you tried the things in the KB article? > [quoted text clipped - 67 lines] >> > >> > 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 [quoted text clipped - 82 lines] > > > > Dave Peterson SignatureDave Peterson I've been playing with Excel 2007 for 9 months. I've never seen this before. >I don't know. > [quoted text clipped - 111 lines] >> > >> > 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. > [quoted text clipped - 117 lines] > > > > Dave Peterson SignatureDave Peterson 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. SignatureRegards Roger Govier > I've been playing with Excel 2007 for 9 months. I've never seen this > before. [quoted text clipped - 118 lines] >>> > >>> > Dave Peterson 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 > [quoted text clipped - 135 lines] >>>> > >>>> > Dave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.