|
Sub 合并()
Dim ar As Variant
Dim br()
Dim i As Long
ReDim br(1 To 50000, 1 To 7)
For Each sh In Sheets
If sh.Name <> ActiveSheet.Name Then
r = sh.Cells(Rows.Count, 2).End(xlUp).Row
ar = sh.Range("a1:g" & r)
For i = 7 To UBound(ar)
If ar(i, 2) <> "" Then
N = N + 1
br(N, 1) = ar(3, 6)
br(N, 2) = ar(2, 6)
br(N, 3) = ar(2, 2)
br(N, 4) = ar(i, 1)
For j = 5 To 7
br(N, j) = ar(i, j)
Next j
End If
Next i
End If
Next sh
If N = "" Then MsgBox "没有需要汇总的数据!": End
With ActiveSheet
.[A1].CurrentRegion.Offset(1).Borders.LineStyle = 0
.[A1].CurrentRegion.Offset(1) = Empty
.[A2].Resize(N, UBound(br, 2)) = br
.[A2].Resize(N, UBound(br, 2)).Borders.LineStyle = 1
End With
MsgBox "ok!"
End Sub
|
|