Excel Crashes when I insert a row on certain worksheets

Question

I am using a pre-formatted workbook I designed with several local and global named ranges in Excel 2007. I have no problem inserting/deleting rows or columns when I first open the original version of the workbook. However, after I save it (with a new file name), close it, and re-open it. I cannot insert/delete rows or columns, group/ungroup rows, etc. without Excel crashing. By crashing, I mean I get the form that states "Microsoft Excel has encountered a problem and needs to close..."

Here is some of the troubleshooting I've tried:

1. If I insert a new, blank worksheet into the saved, re-opened and troublesome file, I can insert rows no problem. So, my conclusion is it's not the workbook, only the pre-formatted worksheets with local range names.
2. I asked a colleague to try using this file on her computer, and she encounters the same issue as me. So, it's not necessarily my computer.
3. I added a lot of named ranges of various types to a new, blank workbook, saved it, re-opened it, and had no issues inserting rows.
4. The troublesome worksheets are not protected.
5. There are no external links in the workbook.
6. I typed "Option Explicit" at the top of every Worksheet and Module in VBA.
7. I've tried changing to Manual Calc mode and Auto Calc mode.
8. None of the problematic worksheets has any VBA code behind it.
9. I tried inserting a row onto one of the troublesome worksheets via VBA code, and I got the following error message: "Run Time Error '-2147417848 (80010108)': Automation error The object invoked had disconnected from its clients." And then Excel freezes, and I have to close using Task Manager, End Task
10. I've tried saving as .xlsx and .xlsm files

As I said, I am able to insert rows when I first initiate the file. It's only when I save it, close, it and re-open it that I cannot insert rows.

My hunch is it has to do with the range names on the worksheets causing the problems, but I guess it could be anything.

Could it be something is happening to those worksheets during the save action?

Please help.

Thanks.

Answer 1

i had this problem and excel resolved it by deleting one of my conditional formatting rules. i went back to the original file to take a look at this rule, and the affected cells had a strange range selected, so i entered the appropriate range again and the problem went away. hopefully it doesnt come back.

Answer 2

Same problem and solution here.

Having the conditional formatting on all the rows is no problem and it automatically changes the range of the CF to include it. When CF is applied seperately for each row excel crashes when adding a new row at the end of the table. For some reason it doesn't know if to include the row in CF or not. Bad coding I guess.

Adding a row between rows in the table goes fine though.

I changed the CF to be able to put it on all table rows simultaneously (help columns) and the problems disappeared.

Answer 3

i had this problem and excel resolved it by deleting one of my conditional formatting rules. i went back to the original file to take a look at this rule, and the affected cells had a strange range selected, so i entered the appropriate range again and

the problem went away. hopefully it doesnt come back.

Thanks for the advice. I couldn't figure it out for hours until I deleted conditional formatting from the sheet I was having problems with. I'm guessing that it happened because I was copying cells from one worksheet that had a column conditionally formatted.

Answer 4

Thank you Rouand Sido! Tedious is a great way to describe this. I am changing my post.

After removing merged cells the problem came back again. I think I have found the problem in my case.

I was doing an add row to a table as listobject. I did not specify a row to the Add function. The original failing line was

        Set NewRow = .ListRows.Add(AlwaysInsert:=True)

I changed this to specify the last row as follows. I use the following code inside a with block that specifies the table as a listobject. 

        lastrow = .ListRows.Count

        If lastrow = 0 Then

            lastrow = 1

        End If

        Set NewRow = .ListRows.Add(lastrow, AlwaysInsert:=True)

I have spent several hours testing this and it has not failed. I hope this helps someone else. I truly hope someone at Microsoft is paying attention and that they will correct the source of the problem. Too many hours are being wasted.

Steve

Answer 5

Rouand has the solution.  This is what was causing my issue as well.  A row with merged cells was crashing excel on the paste.  Doing that one row separately avoided the crash.  THANKS!!

Bizarre, tedious and frustrating reason and solution.

I suffered this problem in Excel 2016, previous version was office 2012. the problem occurs when you insert columns in a spreadsheet where you have merged cells in the columns next to the column you want to insert (such as having cells O2:P:2 or O1:O2 are merged and you want to insert a column after column B)

the solution is un-merge the cells (preferably all cells) you can do that by right clicking merge cells button and select un-merge all