Free Microsoft Excel 2013 Quick Reference

Cannot insert Column

When I try to insert a column, the option is grayed out - it won't let me.

Is this an issue with security that I may have inadvertantly changed? I
made no conscious effort to do this, and can't fix. The worksheet is my own.


Post your answer or comment

comments powered by Disqus
I'm running a macro that runs multiple webqueries.... pulling the table data onto the same active sheet.

After it runs thru x number of times, I get a a 1004 - Cannot insert columns because column IV has data etc...

Looking at the active sheet i see that with evey new web query, the old one is pushed a few columns to the right, which eventually fills the page.

How can I avoid this?

Here is the code I'm using to insert the webquery
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;" & webaddy, _

.BackgroundQuery = False
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = False
End With

I am trying to insert columns on a busy workbook. It will let me insert Rows but when trying to insert columns I get a message “Microsoft cannot shift non blank cells off the work sheet” and it wont let me insert. I have taken off date sort/auto filters and freeze panes.

Is it that the workbook has too much data or could there be another reason?


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.


Cannot insert columns in a worksheet - gives an error message:
To prevent possible loss of data, Microsoft Office Excel cannot shift
nonblank cells off the worksheet.
Try to locate the last nonblank cell by pressing Ctrl + End, and delete or
clear all in cells between the last cell and the end of your data. The
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 have followed the instructions, but no help.
any thoughts???


i am trying to insert a column but i get an error message saying cannot remove non blank cell of the worksheet , i have done ctrl end and it takes me to IV 755 but there is nothing in it


my very simple macro keeps failing. It gets to the 2nd line, and just leaves D1 selected, and does nothing else.

It does not break the macro, it simply fails to insert the column and do any of the rest of the macro. So it does not go to debugger or anything, no error messages.

occasionaly it will work properly, typically after it has failed the first time, for each time afterwards it runs the code fully, but obviously because the first time failed, it cannot copy the correct data for the rest, so it is useless.

why is this happening?

 Sub Macro4()
' Macro4 Macro
' Macro recorded 01/08/2008 by
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[1]+1"
    ActiveCell.FormulaR1C1 = "=RC[1]"
    ActiveCell.FormulaR1C1 = "=RC[1]+7"
    Selection.Delete Shift:=xlToLeft
End Sub

Dear All

I would greatly appreciate your help on this, because I am going crazy.

I have a spreadsheet filled with data (no merged cells) and I created a macro to sort the spreadsheet. Everything was working perfectly, until I tried to insert a column using code at the beginning of the macro.

The code is
    Selection.Insert Shift:=xlToRight
When the macro now runs, the first row acts as if a column has been inserted, i.e it shifts to the right, but the remainder of the spreadsheet does not.

I've tried shifting data to a new sheet, creating a new module, inserting another column, nothing works!!! However, if I step-into the macro, after stepping over the code once (where it does as above), when I shift the cursor above the code and step over it again, it inserts the column!

This is driving me to distraction, any thoughts or help would be greatly appreciated!!


"Cannot shift objects off sheet"

Have a large spreadsheet with multiple worksheet tabs, copied from the left. Operations involve lots of cutting & pasting from the "mother" sheet to "daughter" sheets, thus inserting columns, etc. Half of the files have somehow decided they don't accept column inserts?
< error message reads:> "Cannot shift objects off sheet". No insert of course.

What gives? Any experience with this weird error before?

I have never experienced this before. I am trying to insert a row in a spreadsheet. The spreadsheet is a BOM (bill of material) that was imported from IDEAS (an AutoCad type system). It will allow me to insert columns, but when I try to insert a row I get the following message:"TO PREVENT POSSIBLE LOSS OF DATE MICROSOFT EXCEL CANNOT SHIFT NON-BLANK CELLS OFF THE WORKSHEET".
I have cleared all data from my heading columns and tried to insert a row in this area with no data get the same message. I have highlighted the entire document and used the unhide function to make sure there is no data hidden data. HELP!

I'm trying to insert columns into a spread sheet. It lets me insert 2.
From then on it won't let me insert any more.
It says: "to prevent 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 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've tried those things and they don't work.

What does?


Hello heroes,

Long time lurker, first time poster - I have searched EVERYWHERE.

Anyways, the title is just the tip of the iceberg so I will do my best to explain - if I can clear anything up for you further, please ask.
To prelude, it should be noted that I deleted a LOT of information/code, but I believe all of the relevant items are still there. Second, I only taught myself coding about a month ago so please bear with me.

On my attached workbook, I pull information in from an external source to populate Sheet 1 (Rate_Link). Upon pulling in the information, Column G is inserted to create a Concatenation column out of the initial pulled data. Next, data selected by the users from the boxes in Sheets("Main") is sourced to cells in Sheet 9 (Population_Key), then concatenated into Cell $H$1. Using the Concatenation in $H$1, I use a macro attached to a button to apply a formula array of multiple-vlookups [(because 2 values exist for 1 concatenation) from VBA Inserted Column G & Column H on Sheet 1 (Rate_Link)], then the button performs a 'paste special' of sorts to remove the "empty" (blank valued formulas) cells, leaving merely the values "A,B" in Column L. The next step in my process would be to apply a dynamic name to the results in Column L and use it in a Combo Box.

Here's my problem: When I open the workbook, the inserting of column G shifts the multiple vlookup array 1-3 columns to the right to make the array, usually $I$1:$J$? instead of $G$1:$H$?.

NOTE: I deleted the Insert Column G on Workbook Open, because I was torn between leaving the formatting/document proper/'working' and keeping the document authentic (with the on open code).

I know this is a sub-question, but it is directly related to my individual cause and the exact same set of cells:
Further, I wanted to NOT have the code on a button (though it is present and currently working) but on a worksheet change event with Sheet 11, in the event that all 4 criteria are satisfied (no cell = 0). I tried with an ELSE Run or Call Macro (RouteListMacro), but it didn't work (even though the command button with this macro does) so I established a Do Loop Until on the Worksheet Change, but it does not register. I think this issue might be directly related to the Array shift I described above, but I am uncertain. I say this because I initially had it working, but had closed the workbook then reopened and it was no longer working.

Thank you in advance for you help and your contribution to my future knowledge.

Hi Guys,

The old 'Deleting rows in a protected worksheet' chestnut, but columns not rows.

What I have is a worksheet to calculate results for students, for the more advanced students I want to enable the worksheet so they can insert columns, that is not a problem, and they must be able to delete the columns that they insert, but ONLY the columns they insert.

This is a problem as the worksheet contains columns that they must be able to add data into the cells to make the computations work, the column titles are protected but the body of the worksheet has unprotected cells in it.

If they delete any of the original columns, the worksheet becomes meaningless.

Although this explanation is perfectly clear to me, it may not be clear to everyone, so please ask if it's unclear.

I have asked a similar question on another forum, so far with no luck,


At some point I lost the ability to use the Controls toolbox -- probably in going from Excel 2002 -> 2007 -> 2003. I get the message "Cannot insert object."

I had a reference to Microsoft Forms 2.0 Object Library, unreferenced it, re-registered Windows/system32/FM20.dll, and re-referenced it -- no joy.

Thanks in advance for any suggestions.

Don’t even now if what I’m trying to do is possible so if someone has a better approach, please advise.

I have three tables………

The first table, [tblWeekly1] lists delivery requirements in weekly buckets across the multiple columns. The column [week1, 2, etc.] expressed as 0701 is the week and the column [qty1, 2, etc.] is the weekly requirement.

The second table [tblWeekly2] is the same data but laid out differently. Instead of all the weeks and requirements laid out across columns, the are listed in rows. I’ve done this not knowing which is the table would be the best to use.

The third table, [tblWorkdays] lists how many work days in each week by dock.

What I’m trying to accomplish is a query that takes the weekly buckets, divides them by the number of days and creates a dataset that shows the requirements by day across the multiple columns. My sample data consists of two weekly buckets, so the query would need to return 14 columns of requirement data (7 columns per week). The columns don’t require an actual date name, they can be named basically 1,2,3, etc. I’ve tried crosstab queries and such but it seems the part I’m missing is the ability to automatically insert columns.

A couple key points, the weekly requirement tables are given to me in either format from the mainframe. If they are to be manipulated, it would have to be done in Access. The sample data in the attached .mdb is what I will actually be working with but I’ll have 12 weekly buckets and 15000+ line items.

See attached excel file for a better image of explanation. I’ve also attached a .mdb with the sample data.

Thanks in advance for any help and/ or suggestions!

I am trying to add a command button to an Excel (2003) worksheet. However, I keep getting the message "Cannot insert object".

Please can someone suggest possible solutions

Thanks very much

how to insert column without using toolbar or mouse or vba? but using formula or function in worksheet......

Hello, I have the following code but when it comes to the part to insert columns, the macro will stop and an error screen will pop up. The line of code it refers to when you attempt to debug it is in red font below. Any suggestions on how to correct it - basically I am attempting to insert 3 columns. Thanks in advance!

Sheets("BL Data File").Select
Sheets("BL Data File - Scenario").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
ActiveCell.FormulaR1C1 = "B Years"

Hi, I need to insert columns into my file. I have data say in A7 through A20 and I need to insert an empty column to the right of each entry. Thanks for any help!


I am in need of a macro that will scan a worksheet and insert columns and rows. I am working with information that is exported into excel. The problem that I am coming across is that if there is no information for a specific site or interval, the software that I am using excludes it from the exported excel file. What I need for this macro to do is insert a row with the missing interval. (for example, if interval 9:30 is missing, then an entire row should be inserted between 9:00 and 10:00 and then labeled 9:30 on column A. Intervals (rows) should range from 2:30 (row 2, cell A2) to 22:30 (row 42, cell A42). I need the same type of adjustment to be made for the columns. If there is no data for a specific Site, then a column should be inserted between the missing columns and a label should be created with the missing Site #. The column ranges are from (Col B, Cell B1 to Col E, Cell E1).

here is an example of what is exported.

Interval Site 1 Site 2 Site 3 Site 4 Total
7:00 22 13 35
7:30 57 6 47 110
8:00 32 46 59 137
8:30 22 3 85 79 189
9:00 76 43 140 83 342

I have 900+ excel files, with different number of columns. In all the files I want to cut the last three columns and insert them after column "L".
Can I also have the format of the three inserted columns same as Column "L".

Looking forward to your help

Thanks and Regards


Hey, trying to get a macro to perform a function on multiple sheets. The trusty "Recorder" came up with this, but it doesnt quite work.

Sheets(Array("728", "300", "160", "450")).Select
Selection.Replace What:="0", Replacement:=".001", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

Basically want insert a new column then copy/paste Column B to Column C for each sheet in a book. Then just want an All Sheet Find/Replace of whole cells with 0 to .001.


First off I would like to say thanks to anyone who can help me with this problem.

I set-up my scedules in excel with the date running across the top. I have certain colour boxes for when I must book a task and when the taske must be done. This is so I can see everything that must happen in 3 months etc...

I kinda suck at the VBA part but is there a way where I can enter a date in the first column and it goes say 4 months down the row enter the nexts days date. But when it comes to a weekend it will insert a new column for saturday and sunday. Because when I first setup a template its based on buisness days and not weekends. So when I start a new project I have to go add each date manually look at a calender and insert columns if it lands on a weekend.

thanks a ton if this can work

I want to search a column from - to 0 to + and I want to insert columns between the - and + or if their is 0's insert columns between 0 and +

What kind of method formulas can do this

I don't know why, it's simple worksheet. in all other workseets in the
same document everything OK. but only in one I can't insert column.

I do like this: select column B, right mouse click, choose insert and
nothing happens. The same if try in menu: insert-> columns ==>

Maybe some one knows what is the problem?

thanks in advance

best Regards,

Aivaras_Bakanas's Profile:
View this thread:

No luck finding an answer? You could always try Google.