|
集計は、ワークシート関数の SUMIF を使う方法もありますが、
Excelには集計機能というものもあるので、それを使って・・
Sub My集計()
Dim Sh As Worksheet
Set Sh = Worksheets(2)
Application.ScreenUpdating = False
With Worksheets(1)
With .Range("A1").CurrentRegion
.Sort Key1:=.Range("C1"), Order1:=xlAscending, _
Header:=xlYes, Orientation:=xlSortColumns
.Subtotal 3, xlSum, Array(5, 6, 7)
End With
With .Range("B2", .Range("B65536").End(xlUp).Offset(1)) _
.SpecialCells(4)
Intersect(.EntireRow, Range("A:D")).FormulaR1C1 = _
"=R[-1]C"
.EntireRow.Copy
Sh.Range("A2").PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False
.Cells.RemoveSubtotal
.Rows(1).Copy
Sh.Range("A1").PasteSpecial xlPasteValues
End With
Sh.Activate: Sh.Range("A1").Select
With Application
.CutCopyMode = False
.ScreenUpdating = True
End With
Set Sh = Nothing
End Sub
で、どうかな ?
|
|