【マクロ】【相談】Excelブックとマクロを早く動くようにする為には？

【質問】
以下のマクロのコードを早く動くようにしたいです
重さの理由は、２万行×３列×３シートへ関数が入っている事と思わます
また、ブックを開くのも時間がかかるため軽くしたいです


回答は抽象的な回答にてOKです
何かヒントがあれば宜しくお願いします


例
データが入った所だけ、関数をマクロで入れる
データが入っていない所の関数をマクロで削除してから、マクロをスタートする



【マクロの内容】

【手作業】
シート１から３へデータを貼付
関数にてABC列に表示

データはシート１から３まで２０，０００行まで貼り付けられますが

実際３つのシート２０，０００行まで使う事は月１回あるかないかです
ほぼ、シート１から３で、データ件数は３，０００までです

【マクロ】
❶A1セルのオートフィルターをソート
※あ　をのぞく　かつ　空白行をのぞく

❷ソートされたデータを集約シートへ貼付

❶と❷を３回繰り返す



【コード】
Sub 配列split()


Dim シート名 As Variant

シート名 = Split("Sheet1,Sheet2,Sheet3", ",")


Dim i As Integer
Dim privatesheet As Worksheet

For i = 0 To 2

Set privatesheet = ThisWorkbook.Worksheets(シート名(i))

privatesheet.Range("A1:C20000").AutoFilter 1, "<>", xlAnd, "<>あ"


LastRow = Worksheets("集約").Cells(Rows.Count, "a").End(xlUp).Row
LastRow1 = privatesheet.Cells(Rows.Count, "d").End(xlUp).Row


privatesheet.Cells(2, 1).Resize(LastRow1, 3).Copy


Worksheets("集約").Cells(LastRow + 1, 1).PasteSpecial xlPasteValues

Next i

End Sub

No.1 回答者： artoo 回答日時： 2025/05/06 02:24

> 重さの理由は、２万行×３列×３シートへ関数が入っている事と思わます

各セルには、数値や文字列じゃなくてセル関数が入っているということでしょうか？もしそうであれば、止めた方が良いです。セルには数値とか文字列だけ入れて、後は全部VBAでやる。

> また、ブックを開くのも時間がかかるため軽くしたいです

数字が書いてないので、妥当な遅さなのか、異常な遅さなのか判断付かずです。上記のようにセル関数てんこ盛りなら遅いのはしょうがないでしょうね。

VBAの内容ですが、
AutoFilter がそんなに時間が掛かるとは思えない。
PasteSpecial も、xlPasteValues なので、これ以上改善しようもない。

もし、セル関数てんこ盛りなら、
privatesheet.Cells(2, 1).Resize(LastRow1, 3).Copy
のcopyがセル関数やセル書式込みでコピーをメモリー上に作るから遅いのでしょう。「値貼り付け xlPasteValues」はあるけど「値コピー」はないと思います。

セル関数てんこ盛りをやめるのが一番ですが（ファイルを開くのもずっと速くなるし）、どうしても止められない場合は、
Worksheets("集約").Range(範囲指定).Value = privatesheet.Range(範囲指定).Value
だと、値だけが処理対象になるので、「セル情報を全部コピーして値だけペースト」よりは速くなる気がします（未確認）。