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.

text

text

text

text

text

text

text

text

text

text

<tbody>
</tbody>

 

[JoeMo]

Assuming your text uses spaces as the delimiter separating words and is in range A1:A10 which is merged, this should produce the result you want.

Sub UnMergeDown()
Dim S As Variant
Application.ScreenUpdating = False
With Range("A1:A10")  ' change range to suit
    If Not .MergeCells Then Exit Sub
    S = Split(.Cells(1, 1).Value, " ")
    .UnMerge
    .Cells(1, 1).Resize(UBound(S) + 1).Value = Application.Transpose(S)
End With
Application.ScreenUpdating = False
End Sub

 

[Sektor]

Sub MergeUnmerge()


    Dim objData As Object
    Dim arr As Variant
    
    Range("A1").UnMerge
    Set objData = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    With objData
        .SetText Range("A1").Text
        .PutInClipboard
        arr = Split(.GetText, Chr(10))
    End With
    Range("A1").Resize(UBound(arr) + 1) = Application.Transpose(arr)


End Sub

 

[marilyn123]

I just found this link which would actually work perfectly for what I'm doing. I just need to go it to work for me, dont' know if it's because I'm using 2010 instead of an older version. When I get to the part where it says to use the up arrow, nothing happens. Any help?

Also, using VBA is not an option right now.

1. Highlight entire range of cells containing blanks and non-blanks

2. Press Ctrl+G (the goto dialogue)

3. Press Special, select blank cells (note, they must truly be blank, not formulas that evaluate to "")

4. Type "=" (sans quotes) and then hit the up arrow.

5. Press Cltr+Enter to input that formula into all currently selected blank cells.

DONE

 

[Rick Rothstein]

I just found this link which would actually work perfectly for what I'm doing. I just need to go it to work for me, dont' know if it's because I'm using 2010 instead of an older version. When I get to the part where it says to use the up arrow, nothing happens. Any help?

Also, using VBA is not an option right now.

1. Highlight entire range of cells containing blanks and non-blanks

2. Press Ctrl+G (the goto dialogue)

3. Press Special, select blank cells (note, they must truly be blank, not formulas that evaluate to "")

4. Type "=" (sans quotes) and then hit the up arrow.

5. Press Cltr+Enter to input that formula into all currently selected blank cells.

DONE

After Step #3... are there any cells highlighted?

 

[JoeMo]

I just found this link which would actually work perfectly for what I'm doing. I just need to go it to work for me, dont' know if it's because I'm using 2010 instead of an older version. When I get to the part where it says to use the up arrow, nothing happens. Any help?

Also, using VBA is not an option right now.

1. Highlight entire range of cells containing blanks and non-blanks

2. Press Ctrl+G (the goto dialogue)

3. Press Special, select blank cells (note, they must truly be blank, not formulas that evaluate to "")

4. Type "=" (sans quotes) and then hit the up arrow.

5. Press Cltr+Enter to input that formula into all currently selected blank cells.

DONE

I don't think the procedure you show here will work for merged cells, and it has nothing to do with the version of Excel you are using. In reading it I went back to your OP and realized that you want to repeat the whole line in each cell - not split the line among the merged cells. 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

 

[JoeMo]

Try this:
1. Select the merged cells.
2. Unmerge the cells (Home>Alignment>Merge & Center>Unmerge Cells
3. Hold down Ctrl key and press D key

 

[ParamRay]

Public Sub UnmergeSelection()
  ' Unmerges the selected merged cells
  ' and repeats the value in each cell
  
  Dim MergedCells As Range
  Dim CellValue As Variant
  
  On Error GoTo ErrHandler
  If Not TypeOf Selection Is Range Then
    MsgBox "Selection is not a range of cells.", vbExclamation
    Exit Sub
  End If
  
  ' Null occurs when selection has
  ' both merged and unmerged cells
  If IsNull(Selection.MergeCells) Then
    MsgBox "Selection is not a merged range of cells.", vbExclamation
    Exit Sub
  End If
  
  If Not Selection.MergeCells Then
    MsgBox "Selection is not a merged range of cells.", vbExclamation
    Exit Sub
  End If
  
  Set MergedCells = ActiveCell.MergeArea
  CellValue = MergedCells.Item(1).Value
  MergedCells.Unmerge
  MergedCells.Value = CellValue
  Exit Sub
  
ErrHandler:
  MsgBox Err.Description, vbCritical
End Sub

 

[marilyn123]

After Step #3... are there any cells highlighted?

Yes, all the blank cells in my column are highlighted.

 

[marilyn123]

The only thing odd about my spreadsheet that I notice is that the title of the document says [group] when it's open. If I copy the data to a new workbook, the title of the document still says "Book2 [Group]". Could that be an issue? I have no idea why that is.