Inserting Merged Rows (’97 2000)

Topic

New Reply

I have several fairly large Spreadsheets (4000 rows) with cells merged so that they are three high (to allow consistent format in printing).

When inserting a row into middle of Spreadsheet, the insertion will work, but the next action of attempting to move cursor
etc. always results in a “Program has executed an illegal instruction and program will be terminated” or something to that
effect. At this point the data are lost.

Strangely, inserting into top row does not cause this reaction.

Is this a known bug? It certainly repeats for me on several different machines.

I realize that the answer to this is of the nature, “Well if you know what causes crash, don’t do that!”

But if there is a work-around I would like to know as I frequently do have the need to Insert rows.

Thomas Cox

Reply | Quote

Replies

My advice in cases like this is not going to please you I’m afraid:

Avoid merging of cells as much as you can. I would prefer to try and change formatting in such a way that the cells *look* merged, but aren’t.
Merged cells are reknown for causing trouble when copying and pasting (as you’ve seen ).

Reply | Quote

Just to echo what Jan Karel’s post says, here’s a macro I keep in Personal.xls, which shows what I do with merged cells in WB’s I receive!

Sub UnMerge()
Intersect(ActiveSheet.UsedRange, Selection).MergeCells = False
End Sub

Reply | Quote

JohnBF

Can you elaborate? I am a complete newbie. When I try to run Unmerge Cells I get a Compile Error. Sub or Function not defined. Thanks

Reply | Quote

, code should run OK provided that the selection and used range in fact intersect. Check for typos?

1. Open the VBE (Alt-F11) and see that Tools, References has at least the following checked: Visual Basic for Applications, Microsoft Office x.x Object Library, Microsoft Excel x.x Object Library, OLE Automation. I also have the Forms Object x.x Library, don’t think you need that for this.

2. Attach is a workbook for you to look at.

3. This version handles the error caused by the selection and used range not overlapping:

Sub UnMerge()
If Not Intersect(ActiveSheet.UsedRange, Selection) Is Nothing Then _
Intersect(ActiveSheet.UsedRange, Selection).MergeCells = False
End Sub

4. If you’re using both 97 and 2000, check that the error is the same in both.

Reply | Quote

Being stubborn as I am , I still think the problem is caused by the cells being merged. It may be a bug, dunnno really, but merged cells are tricky stuff, especially when you start moving and copying them about. I tend to keep away from merging cells and putting borders around sets of cells instead, making them appear as one.

Reply | Quote

Thank you. All is working now

Reply | Quote

If you were interested in that code, you might also be interested in the third bit of code in post 68243, which emulates the old center-across-selection toggle -without merge- that was in XL 5/95. You can put the code into your existing Personal.xls, copy the existing merge and center button and attach the macro, then delete the standard merge-& center button.

I should write a version of the unmerge macro that unmerges but sets center-across-selection on for the cells formerly merged.

Reply | Quote

It sounds to me like you may have a corrupted cell somewhere on the worksheet. Make a backup copy of the workbook and then try the following:

1- Use File SaveAs to save the file in HTML format. Then close Excel.

2- Start Excel and open the HTML version of the file.

3- Use File SaveAs to save the file as a normal workbook (.xls) file. Then close Excel.

4- Start Excel again and see if the problem still occurs. If not, check the file for any formatting that may have been lost in the above process.

Reply | Quote

I can only comment on version 97. But it depends on which way you are inserting the row.

Which method are you using to insert a row? Try to right-click a cell that is in the “middle” row of a merged group. So, if you have cells A20 through A22 merged, right-click cell B21. Then choose Insert from the pop up menu. In the dialog box, choose Entire Row. See if that works.

I say this because using the common method of right clicking row 21’s heading (gray area with 21 on it) and choosing Insert won’t work in 97 with a merged cell involved. But the above method works great.

Having said that, I agree with the other person that merged cells are an evil last resort.

-Lenny

Reply | Quote