Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Easley, South Carolina, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inserting Merged Rows ('97 2000)

    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

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting Merged Rows ('97 2000)

    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 <ws>).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Inserting Merged Rows ('97 2000)

    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
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting Merged Rows ('97 2000)

    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.
    Legare Coleman

  5. #5
    New Lounger
    Join Date
    Jul 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting Merged Rows ('97 2000)

    JohnBF

    Can you elaborate? I am a complete newbie. <img src=/S/confused.gif border=0 alt=confused width=15 height=20> When I try to run Unmerge Cells I get a Compile Error. Sub or Function not defined. Thanks <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Inserting Merged Rows ('97 2000)

    <img src=/S/hmmn.gif border=0 alt=hmmn width=15 height=15>, 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.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting Merged Rows ('97 2000)

    Being stubborn as I am <g>, 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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Memphis, Tennessee, USA
    Posts
    243
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting Merged Rows ('97 2000)

    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. <img src=/S/frank.gif border=0 alt=frank width=15 height=15>

    -Lenny

  9. #9
    New Lounger
    Join Date
    Jul 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inserting Merged Rows ('97 2000)

    Thank you. All is working now <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Inserting Merged Rows ('97 2000)

    If you were interested in that code, you might also be interested in the third bit of code in <post#=68243>post 68243</post#>, 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.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •