Unmerging cells - text

[marilyn123]

I have a merged cell with a single line of text in it going for example from A1:A10. I would like to unmerge these cells but have that line of text populate each cell. Otherwise we have to unmerge/copy/paste over and over again.

is there a way to do this? the below is what I would want it to look like when I'm done.

[TABLE="width: 500"]
<tbody>[TR]
[TD]text[/TD]
[/TR]
[TR]
[TD]text[/TD]
[/TR]
[TR]
[TD]text[/TD]
[/TR]
[TR]
[TD]text[/TD]
[/TR]
[TR]
[TD]text[/TD]
[/TR]
[TR]
[TD]text[/TD]
[/TR]
[TR]
[TD]text[/TD]
[/TR]
[TR]
[TD]text[/TD]
[/TR]
[TR]
[TD]text[/TD]
[/TR]
[TR]
[TD]text[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

I understand you can't use VBA, but for the record, below is revised code that will do what you requested in your OP.

Sub UnMergeDown()
Application.ScreenUpdating = False
With Range("A1:A10")  ' change range to suit
    If Not .MergeCells Then Exit Sub
    .UnMerge
    .Cells(1, 1).Resize(.Rows.Count).Value = .Cells(1, 1).Value
End With
Application.ScreenUpdating = False
End Sub

I see two problems with your code as written...

1) I the specified range for the With statement is not a set of merged cells, you exit the sub; however, ScreenUpdating was turned off, and so when you exit the sub, it will remain off.

2) If the specified range for the With statement is a range of cells where only some of them are merged, the .MergeCells call will return Null, not False, which means the If test will not pass and execution will drop down to the .Unmerge method call... this will unmerge any merged cells and then assign the value in the first cell for the specified range to all the specified cells whether they were part of a merge or not.

I think the following modification to your code solves these problems...

Sub UnMergeDown()
  With Range("A1:A10")  ' change range to suit
    If .MergeCells Then
      Application.ScreenUpdating = False
      .UnMerge
      .Cells(1).Resize(.Rows.Count).Value = .Cells(1).Value
      Application.ScreenUpdating = False
    End If
  End With
End Sub

 

[marilyn123]

Ok, I think I got my issue figured out. Instead of using the up arrow, I just input the cell A2 into the formula bar, and then used CTRL + ENTER to get the other blank cells to copy the relative formula.

Thanks everyone!!

 

[JoeMo]

I see two problems with your code as written...

1) I the specified range for the With statement is not a set of merged cells, you exit the sub; however, ScreenUpdating was turned off, and so when you exit the sub, it will remain off.

2) If the specified range for the With statement is a range of cells where only some of them are merged, the .MergeCells call will return Null, not False, which means the If test will not pass and execution will drop down to the .Unmerge method call... this will unmerge any merged cells and then assign the value in the first cell for the specified range to all the specified cells whether they were part of a merge or not.

Problem # 2 is irrelevant in the context of the OP which specifies an exact range for which all cells in the range are merged.

 

[Rick Rothstein]

Problem # 2 is irrelevant in the context of the OP which specifies an exact range for which all cells in the range are merged.

In the message where you posted your code you said "I understand you can't use VBA, but for the record...". Also, what if the user simply mistypes the range that they want? In any event, I believe the way I modified your code should eliminate both of the problems I mentioned.

 

[JoeMo]

In the message where you posted your code you said "I understand you can't use VBA, but for the record...". Also, what if the user simply mistypes the range that they want? In any event, I believe the way I modified your code should eliminate both of the problems I mentioned.

Yes, your modification does eliminate both problems, but I wrote the code to address the OP's description of what was wanted,wherein problem #2 is not relevant, not for your more general case.

 

[Rick Rothstein]

Yes, your modification does eliminate both problems, but I wrote the code to address the OP's description of what was wanted,wherein problem #2 is not relevant, not for your more general case.

Okay, no problem... I had simply over-interpreted your "for the record" comment to mean a more general solution where the OP's request would have been a special case.