Error when try to insert column

midann

New Member
Joined
Apr 16, 2002
Messages
18
Hi,

I'm getting an error message when I try to insert a column in the spreadsheet. There are 3 worksheets in the workbook. Each spreadsheet represents a sales rep. A list
of businesses companies is in column A. Each week Column B is highlighted, a column is inserted to add that week's point totals to the spreadsheet; therefore, column B has the most current week with columns C, D, ....
having the previous weeks points.

When I went in and highlighted column B to insert a column, I got an error message. It read, "To prevent possible loss of data, Microsoft Excel cannot shift nonblank cells off the worksheet. Try to delete or clear the cells to the right and below 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 cannot insert columns anywhere at all, but oddly enough the last worksheet in the workbook will let me insert. As of now, the worksheet only goes up to column F and I'm only using 40 rows so it's not a large worksheet.

How can I get the ability to insert columns back?

Your help would be greatly appreciated.

Ann
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sounds to me like you have data entered in at least one cell in every column from A to IV, and when you try to insert a new column, the last column will be eliminated, hence the message. Check for contents in column IV.
 
Upvote 0
Barry,

Sorry, that's not it-this is a fairly new
spreadsheet and there's no data from column
G up to column IV.

Any other ideas?

Ann
 
Upvote 0
Even if you don't see any content outside your main data range, try this:

Select Columns G through IV and hit Editclearall. Do the same for Rows under your data range. You may be able to insert columns again.

A shot in the dark from your neighborly Asala42.

HTH
 
Upvote 0
I've selected the columns and rows around the data range and done the Edit/Clear/All,
and no luck. In trying to figure this out,
I remembered that the manager that gave me this spreadsheet said it originally came from
quattro pro. The file was converted from
quattro pro to excel. One may want to think
the conversion is doing this, but why would
it work for awhile, then suddenly not?
 
Upvote 0
As Asala says, you might have inadvertantly gotten something entered into a cell in column IV. Even if it is a space, and doesn't seem to appear on the screen, it is still a data entry and, when you try to insert a column, you will get that message.

You can try Asala's idea, or you can also highlight all of the columns to the right of your list and delete them. Same with the rows below your list. Try it.
 
Upvote 0
Just saw your reply to Asala that you cleared all data and it still doesn't work. But it's not only data in the last column that produces that message, it is also certain types of formatting in that column, such as borders or patterns. Try my suggestion to delete all of the columns to the right.
 
Upvote 0
Ok I think I got it. I pulled up a blank file and copied the blank file's format (via format painter) over the formats of the sheet on your file. I can now insert columns.

I really don't know for sure why editclearall did not work. I noticed some odd behavior - editclear removed gridlines for some reason despite settings in toolsoptions. I also noticed several data points had spaces in front (I'm not sure if that is what was planned.)

HTH
Adam
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top