フィルタでくくった状態でコピー 貼付 マクロ

質問

Ｋ列までデータが入っていて
Range("L2").Value = "=SUMIF(B:B,B2,H:H)"
として、
Ｋ列には、=IF(B2=B1,0,1)という感じで関数が入ります。
該当「１」でフィルタでくくってる状態です。

Ｌ２をコピーしてＫ列の最下部(データがある最終行)までコピーしたいと思ってますが、
コピーするとくくってない「１」ではない部分にも関数が張り付いてしまいます。

その後、フィルタを解除して、Ｌ２を値貼付したいと思ってます。(もちろん、マクロで自動化して)

このようなことは可能でしょうか？

回答No.2

end-u · ベストアンサー

>くくってる部分のみを値貼付
くくってる部分＝抽出後の可視セル。
数式コピー後、可視セルのみLoopして値貼付すれば良いです。

Dim r As Range
For Each r In Range("L2", Cells(Rows.Count, "K").End(xlUp).Offset(, 1)).SpecialCells(xlCellTypeVisible)
　　r.Value = r.Value
Next
みたく。
この場合フィルタ解除は要りませんが、可視セルが多いともたつきます。

まとめて処理したいなら
下記のようにして、解除後に数式セットとか。
Sub test2()
　　Dim r　As Range
　　Dim rr As Range

　　With ActiveSheet
　　　　Set r = .Cells(.Rows.Count, "K").End(xlUp)
　　　　If r.Address <> "$K$1" Then
　　　　　　Set r = .Range("L2", r.Offset(, 1))
　　　　　　Set rr = r.SpecialCells(xlCellTypeVisible)
　　　　　　If .FilterMode Then
　　　　　　　　.ShowAllData
　　　　　　End If
　　　　　　rr.Formula = "=SUMIF(B:B,B2,H:H)"
　　　　　　r.Value = r.Value
　　　　End If
　　End With

　　Set r = Nothing
　　Set rr = Nothing
End Sub

お礼

本当にハンパなくありがとうございました。

こんなことが出来るとは
offset関数ってすごいんですね？￥

回答No.1

end-u

>コピーするとくくってない「１」ではない部分にも関数が張り付いてしまいます。
再度確認してみてください。
フィルタ状態でも
Range("L2").Copy Range("L2", Cells(Rows.Count, "K").End(xlUp).Offset(, 1))
これで可視セルのみコピーになります。

ついでに値貼付まで書いてみると
Sub test()
　　Dim r As Range
　　With ActiveSheet
　　　　Set r = .Range("L2", .Cells(.Rows.Count, "K").End(xlUp).Offset(, 1))
　　　　r.Item(1).Copy r
　　　　If .FilterMode Then
　　　　　　.ShowAllData
　　　　End If
　　　　r.Value = r.Value
　　End With
　　Set r = Nothing
End Sub
こんな感じです。

補足

ハンパないっす
ありがとうございます！！！

ただ、１点だけ、再計算を２度してしまうのは、しょうがないのでしょうか？

コピーを貼り付けたときと、フィルタを解除したときと両方でってのは…

コピーみたいに、くくってる部分のみを値貼付ってのはやっぱり無理何でしょうか？