【マクロ】【相談】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.4 回答者： KenKen_SP 回答日時： 2025/05/11 17:03

こんにちは。

ブックを開く時点で時間がかかるのは外部リンクの自動更新が大量に発生しているためと予測します。
計算不要、あるいは数式やリンクである必要のないものは値に貼り付け直して計算量を減らす設計にした方が良いでしょう。

その上でマクロの実行速度が遅いというのであれば、シートの自動計算をとめて、処理終了後にまとめて再計算するようにします。

https://learn.microsoft.com/ja-jp/office/client- …

No.3 回答者： artoo 回答日時： 2025/05/07 18:26

> やはり、セル関数をなくすしかないですよね

そうですね。

> formulaプロパティで、つど、必要な関数だけ、入れる等しかないですかね
> 最初から、２万行×３シート分用意するのではなくてですね

「今は2万行分用意してあるが、実際に使うのは100行程度なので、そこだけセル関数を入れる」というのはありです。というか、「何故、使わないのに2万行分もセル関数入れた？？」と問い詰めたいところです。

ただ、「最初は100行程度だけけど、どんどん増える一方でそのうち2万行近くなる」なら、増えるまでの間の一時しのぎの対策なので、根本的な対策としては私がNo1の回答に書いたとおり、「セル関数を一切使わないで、全部VBAだけで書く」しかないでしょうね。

ファイルを開くのに時間が掛かるのは我慢するが、VBAの処理時間だけでも速くしたいのであれば、これもNo1に「どうしても止められない場合は」と書いた対策で多少は速くなるかも知れません。書いた通り未確認なので、あまり変わらないかも。

No.2 回答者： fujillin 回答日時： 2025/05/06 22:27

こんばんは

原因が特定できていないようなので、対処法もわかりませんけれど・・

仮に、関数が理由だとしてもご提示程度の簡単な関数であれば、「ブックを開くのも時間がかかる」とも思えないのですが、どこかで面倒な関数式が多数利用されていたりしませんか？
実際の内容が、ご提示の様な関数であってそれが原因なら、マクロで直接D:F列を参照するようにすれば関数は不要にも思いますけれど・・？
（実際の内容が不明なのでわかりませんけれど）

また、処理にどの程度の時間がかかっていて「遅い」と判断しているのかも不明ですし、全体の情報が漠としているので、はっきりとはしませんが、一応「効果があるかもしれない」方法での処理を以下に例示しておきます。

※ 各シートとも1行目はタイトル行と仮定しました。
※ 試してみて、差がなければ捨ててください。


Sub testSample()
Dim rg As Range
Dim i As Long
Dim v

With Worksheets("集約")
Set rg = .Cells(2, 1)
For Each v In Array("Sheet1", "Sheet2", "Sheet3")
rg.Resize(19999, 3).Value = Worksheets(v).Range("A2:C20000").Value
Set rg = .Cells(Rows.Count, 1).End(xlUp).Offset(1)
Next v
rg.Resize(20000, 3).ClearContent

If rg.Row > 2 Then
v = .Cells(1, 1).Resize(rg.Row - 1).Value
For i = 2 To UBound(v)
If v(i, 1) = "" Or v(i, 1) = "あ" Then Set rg = Union(rg, .Cells(i, 1))
Next i
rg.EntireRow.Delete
End If
End With
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
だと、値だけが処理対象になるので、「セル情報を全部コピーして値だけペースト」よりは速くなる気がします（未確認）。

この回答へのお礼

お返事ありがとうございます
セルに普通の長い関数が入ってます

関数の種類が多すぎて、書けないので
簡単関数にて表現しました

やはり、セル関数をなくすしかないですよね

formulaプロパティで、つど、必要な
関数だけ、入れる等しかないですかね

最初から、２万行×３シート分用意
するのではなくてですね

お礼日時：2025/05/07 11:17