|
>計算式を設定
というのは、B列の小計を入れるという意味でしょーか ? それならこんな感じで。
Sub MyFilter()
Dim MyR As Range, SmR As Range
Dim i As Long
Application.ScreenUpdating = False
If Range("A1").CurrentRegion.ListHeaderRows = 0 Then
Rows(1).Insert xlShiftDown
Range("A1:B1").Value = Array("Data1", "Data2")
End If
Set MyR = Range("A2", Range("A65536").End(xlUp)).Offset(, 1)
Range("A:A").AutoFilter 1, "<>9901"
Set SmR = MyR.SpecialCells(12)
Range("A:A").AutoFilter 1, "9901"
On Error GoTo ELine
Set MyR = MyR.SpecialCells(12)
On Error GoTo 0
For i = 1 To MyR.Areas.Count
MyR.Areas(i).Formula = "=SUM(" & SmR.Areas(i).Address & ")"
Next i
ELine:
ActiveSheet.AutoFilterMode = False
If Range("A1").Value = "Data1" Then Rows(1).Delete xlShiftUp
Application.ScreenUpdating = True
Set MyR = Nothing: Set SmR = Nothing
End Sub
|
|