|
本帖最后由 xdragon 于 2014-11-14 21:34 编辑
张雄友 发表于 2014-11-14 21:25
用1楼和 3楼的 附件测试同样,合并后,日期这列都不见了。
Sub 不等列合并()
Dim sh As Worksheet, arr, brr(0 To 1000000, 0 To 30), w As WorksheetFunction '定义100万行,30列。
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
'On Error GoTo 100
Set d = CreateObject("scripting.dictionary")
Set w = Application.WorksheetFunction
MyName = Dir(MyPath & "*.xls*")
Do While MyName <> ""
If MyName <> ThisWorkbook.Name Then
With GetObject(MyPath & MyName)
For Each sh In .Worksheets
If w.CountA(sh.UsedRange) Then
Set c = sh.UsedRange
arr = c.Resize(sh.Cells(sh.Rows.Count, c.Column).End(xlUp).Row - c.Row + 1, c(1, 1).Offset(, sh.Columns.Count - 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 > 1048575 Then
MsgBox "超出最大行数1048576,无法合并"
Exit Sub
End If
brr(m, d(arr(1, 1))) = arr(i, 1)
brr(m, 0) = sh.Name '表名
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
brr(0, 0) = "表名" '表名
If m Then [A1].Resize(m + 1, n + 1) = brr
'100:
End Sub
其实你只要逐步运行就能看出来端倪了 ,发现一个错误改一次,你的这个代码就有三个明显的错误,(*^__^*) 嘻嘻 |
|