若EXCEL工作表有很多合并的单元格，要将所有合并的单元格撤销，并填充撤销合并前显示的内容，这是一项很繁琐且容易出错的工作。但可通过宏程序可轻松准确地搞定，方法如下：

一、实现该功能的Excel宏程序(复制即可)

Private Sub CXHB()

Dim i As Range

For Each i In ActiveSheet.UsedRange

If i.Address <> i.MergeArea.Address And i.Address = i.MergeArea.Item(1).Address Then

i.MergeArea.Select

i.MergeArea.UnMerge

' MsgBox (i.MergeArea.Address)

Selection.FillDown

End If

Next i

End Sub

原贴地址——http://blog.sina.com.cn/s/blog_558ddcab0101idcn.html

二、合并多个相同内容的excel文件

Sub 合并当前目录下所有工作簿的全部工作表()

Dim MyPath, MyName, AWbName

Dim Wb As Workbook, WbN As String

Dim G As Long

Dim Num As Long

Dim BOX As String

Application.ScreenUpdating = False

MyPath = ActiveWorkbook.Path

MyName = Dir(MyPath & "\" & "*.xls")

AWbName = ActiveWorkbook.Name

Num = 0

Do While MyName <> ""

If MyName <> AWbName Then

Set Wb = Workbooks.Open(MyPath & "\" & MyName)

Num = Num + 1

With Workbooks(1).ActiveSheet

.Cells(.Range("B65536").End(xlUp).Row + 2, 1) = Left(MyName, Len(MyName) - 4)

For G = 1 To Sheets.Count

Wb.Sheets(G).UsedRange.Copy .Cells(.Range("B65536").End(xlUp).Row + 1, 1)

Next

WbN = WbN & Chr(13) & Wb.Name

Wb.Close False

End With

End If

MyName = Dir

Loop

Range("B1").Select

Application.ScreenUpdating = True

MsgBox "共合并了" & Num & "个工作薄下的全部工作表。如下：" & Chr(13) & WbN, vbInformation, "提示"

End Sub