Sub 合并()
Dim sh As Worksheet, arr, brr(0 To 65535, 1 To 255)
Dim d As Object, i&, j&, m&, n&, c As Range, MyPath$
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = ThisWorkbook.Path & "\"
If .Show = False Then Exit Sub
MyPath = .SelectedItems(1) & "\"
End With
Application.ScreenUpdating = False
Set d = CreateObject("scripting.dictionary")
d("单位名称") = 1
brr(0, 1) = "单位名称"
n = 1
MyName = Dir(MyPath & "*.xls")
Do While MyName <> ""
If MyName <> ThisWorkbook.Name Then
With GetObject(MyPath & MyName)
For Each sh In .Worksheets
If Application.CountA(sh.UsedRange.Cells) > 0 Then
Set c = sh.UsedRange.Find("单位名称", , , 1)
arr = c.Resize(sh.Cells(65536, c.Column).End(xlUp).Row - c.Row + 1, c.Offset(, 256 - c.Column).End(xlToLeft).Column)
For j = 1 To UBound(arr, 2)
If Len(arr(1, j)) Then
If Not d.Exists(arr(1, j)) Then
n = n + 1
d(arr(1, j)) = n
brr(0, n) = arr(1, j)
End If
End If
Next
For i = 2 To UBound(arr)
m = m + 1
If m > 65535 Then
MsgBox "超出最大行数65536,无法合并"
Exit Sub
End If
brr(m, 1) = arr(i, 1)
For j = 2 To UBound(arr, 2)
If Len(arr(1, j)) Then brr(m, d(arr(1, j))) = arr(i, j)
Next
Next
End If
Next
.Close False
End With
End If
MyName = Dir
Loop
Application.ScreenUpdating = True
Cells.ClearContents
If m Then [A1].Resize(m + 1, n + 1) = brr
End Sub