How to unmerge cells and replicate the result in all the (previous) merged cells

[gustavobarbaran]

Hello,


I'm working with several workbooks and they have merged cells in vertical and horizontal ways. I need to un merge those cells and to replicate the text inside the merged cell in all the others cells. How can I do that?

Thanks

 

[Montrey]

Select cells. Press Alt+e+a+a.

After that.

Select cells. Ctrl+C

then Ctrl+V where you want replicated.

 

[Luke M]

Here's a macro that should do your work faster for you. Finds all merged cells within a worksheet, unmerges, and fills in values.

[pre]

Sub KillMerges()
Dim c As Range
Dim xCell As Range
Dim xValue As Variant
Set c = FindMergedCells(ActiveSheet.Cells)
Application.ScreenUpdating = False
If Not c Is Nothing Then
For Each xCell In c
xCell.Select
xValue = xCell.Value
xCell.UnMerge
Selection = xValue
Next
End If
Application.ScreenUpdating = True
End Sub

Function FindMergedCells(RangeToSearch As Variant) As Range
Dim MergedCell As Range, FirstAddress As String
If TypeName(RangeToSearch) = "String" Then Set RangeToSearch = Range(RangeToSearch)
Application.FindFormat.MergeCells = True
Set MergedCell = RangeToSearch.Find("", LookAt:=xlPart, SearchFormat:=True)
If Not MergedCell Is Nothing Then
FirstAddress = MergedCell.Address
Do
If FindMergedCells Is Nothing Then
Set FindMergedCells = MergedCell
Else
Set FindMergedCells = Union(FindMergedCells, MergedCell)
End If
Set MergedCell = RangeToSearch.Find("", After:=MergedCell, LookAt:=xlPart, SearchFormat:=True)
If MergedCell Is Nothing Then Exit Do
Loop While FirstAddress <> MergedCell.Address And Not MergedCell Is Nothing
End If
End Function

[/pre]
Credit for the function to Rick Rothstein

http://www.excelfox.com/forum/f13/find-merged-cells-vba-310/#post1048

 

[herveleger]

there is no doubt that e-bookasme b31.9 make you the sexy minx you always wanted to be. even if you have to save up the money to buy a hatscheap new era hats, it will be worth it.