Solved: Unmerge cells & Auto-fills

[syybacon]

Hi

1. I have a spread sheet with many cells merged, I am looking for a macro that will automatically unmerge all the cells.

2. When I unmerge the cells, only the first cell will be filled with the original text, all the others are just empty cells. What I needed is to auto-fill the unmerged empty cells with the corresponding texts from the original merged cells.

Any help would be appreciated

Cheers Guys

[jproffer]

That's what happens anyway...by default. Select the range you want to unmerge, or select the whole sheet by clicking the upper left corner box next to the column header and above the row header. Click the merge button and all the cells should unmerge, leaving the text in the upper left cell of the original merged cells.

[syybacon]

That's what happens anyway...by default. Select the range you want to unmerge, or select the whole sheet by clicking the upper left corner box next to the column header and above the row header. Click the merge button and all the cells should unmerge, leaving the text in the upper left cell of the original merged cells.

Thank you for your reply. I am aware of what you said.
I am looking for a macro that do the job for me instead of copy/paste every cell emptied after unmerge.

[jproffer]

OK....now I don't know what you mean. I apologize, maybe it's me...but I don't get it.

If you unmerge several cells that contain a value, or text or whatever...the upper left cell will take on that value, text or whatever...by default. No copy and paste needed...just unmerge, 2 clicks and you can unmerge a whole worksheet.

I'm not following why you think you have to copy and paste anything after you unmerge.

[Aussiebear]

syybacon, attached is a workbook with code that does what jproffer kindly suggested. What do you mean by copy paste every cell emptied after the unmerge?

You suggest that you want the unused portion of the now unmerged cells filled with the corresponding text from the original merged cells.... Please post an example.

[syybacon]

Thank you vey much for your reply. I have found a solution in a previous thread.

Public Sub ProcessData()
Dim cell As Range
Dim MergedCell As Range
Dim NumRows As Long
Dim NumCols As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

For Each cell In Range("A1").Resize( _
.Cells.SpecialCells(xlCellTypeLastCell).Row, _
.Cells.SpecialCells(xlCellTypeLastCell).Column)

If cell.MergeCells Then

NumRows = cell.MergeArea.Rows.Count
NumCols = cell.MergeArea.Columns.Count
cell.UnMerge
If NumRows > 1 Then cell.AutoFill cell.Resize(NumRows)
If NumCols > 1 Then cell.Resize(NumRows).AutoFill cell.Resize(NumRows, NumCols)
End If
Next cell
End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub

[Aussiebear]

I am looking for a macro that do the job for me instead of copy/paste every cell emptied after unmerge.

What has your solution got to do with this part of your request?