|
Sub s()
Dim wh As Worksheet
For Each wh In Worksheets
With wh
Set d = CreateObject("scripting.dictionary")
gs = .Name
arr = .Range("a1:a" & .Cells(Rows.Count, 1).End(3).Row - 1)
For i = 1 To UBound(arr)
If arr(i, 1) = "" Then
arr(i, 1) = arr(i - 1, 1)
End If
Next
.Columns("a:a").UnMerge
.Range("a1").Resize(UBound(arr, 1), 1) = arr
ar = .Range("a1:a" & .Cells(Rows.Count, 1).End(3).Row - 1)
For Each rs In ar
d(rs) = ""
Next
For Each rn In d.keys
w = WorksheetFunction.Match(rn, .Range("a1", .[a1].End(4)), 0)
ws = WorksheetFunction.CountIf(.Range("a1", .[a1].End(4)), rn)
.Range("A" & w).EntireRow.Insert
.Range("A" & w & ":B" & w).Merge
.Range("a" & w) = "119消防二级消防士套式肩章"
.Range("a" & w)(1, 3).Resize(1, 3) = Array("合计", "大号", "小号")
.Range("A" & w + ws + 1).EntireRow.Insert
.Range("A" & w + ws + 1) = "合计"
.Range("A" & w + ws + 1)(1, 3) = WorksheetFunction.Sum(.Range("c" & w + 1 & ":c" & w + ws))
.Range("A" & w + ws + 1)(1, 4) = WorksheetFunction.Sum(.Range("d" & w + 1 & ":d" & w + ws))
.Range("A" & w + ws + 1)(1, 5) = WorksheetFunction.Sum(.Range("e" & w + 1 & ":e" & w + ws))
Next
For i = .Cells(Rows.Count, 1).End(3).Row To 2 Step -1
If .Range("a" & i) = .Range("a" & i).Offset(-1, 0) Then
Application.DisplayAlerts = False
.Range("a" & i).Offset(-1, 0).Resize(2, 1).Merge
Application.DisplayAlerts = True
End If
Next
Next
End With
End Sub
|
|