エクセルマクロの高速化について教えてください

やりたいことは特定シート名の必要な列データを取り出して並び替えることです。複数ファイルがあるので最後に縦に並べ１シートにしたいです。一つのシートに4000行、20列位あります。

今のプログラムは

①ファイルを開く
②特定のシート名のシートを別ファイルにコピーして名前変更
③①と②をファイル数繰り返す
④必要な列を別シートにコピー(ここで並び替え)
⑤一つのシートにまとめる

となってます。ファイル名、シート名コピーする列名は設定シートに記載しています。

④と⑤だけだと少し遅いと感じるレベルですが、全部通すと画面真っ白でしばらく動かなくなります。

先ほど調べて画面描写をやめると早くなるという情報を得ましたので確かめてみるのですが、他に早くする方法はありますか？

そもそも考え方が間違っていて無駄な動きをしている点がありましたら教えてください。

また、列のコピー方法A列で指定するよりも①列で指定する方が早い、シートコピーよりも全データ選択して張り付けた方が早いなどありましたら教えてください

No.3 回答者： WindFaller 回答日時： 2018/10/20 10:27

こんにちは。

直接の回答ではありませんが、
>全部通すと画面真っ白でしばらく動かなくなります。
これは、Excel 2013 ではありませんか？
たぶん、ホワイト・スクリーンとか呼ばれる、一種バグなんだろうと思っています。
海外ではかなり話題になったようですし、海外でもいろんなことを言う人がいますが、私は、直らないのではないかと思います。たぶん、マイクロソフト社は知っていることなのだろうし、
アプリのウィンドウ構造のMDIからSDIという仕組み自体を変えたところに問題があるのだろうと思います。

後は、No.2 さんのおっしゃるものに加えて
With Application
　.ScreenUpdating =False
　.EnableEvents = False
　 .Calculation = xlCalculationManual
End With
(戻す方法は、ご自分でお調べください。)

ただ、データ型の問題は、Variant であろうが、Long 型であろうか、体感するほど大きな違いはないのかと思います。細かく言えば、マイクロソフト社が指導する、『VBAの最適化(optimization)』をご覧になればよいのですが、今どき、守っている人はどの程度いるのか怪しいです。

以下は、アーカイブとして、かろうじて残っているようですが、たぶん、どなたでも収穫はあると思います。
https://docs.microsoft.com/ja-jp/previous-versio …

マクロについては、言葉だけでははっきりしませんが、
シートそのものをコピーする手法が果たして良いのか、なんとも言えません。
あまり繰り返すようなら、シートコピーはやめたほうがよいのではないかと思うぐらいです。
ただ、誰がやっても根本的な解決はしない、ハードやOSの問題を含んでいるのかもしれません。

No.2 回答者： googoo900 回答日時： 2018/10/20 00:05

すべてではありませんが、影響の大きいところで以下です。

１．画面の描画更新をしない。
DIM文の次の行に、Application.ScreenUpdating = False
ENDの手前の行に、 Application.ScreenUpdating = True

２．極力selectしない。
コピーペーストするときはselectは不要です。範囲の場合でもシートの場合でもです。

３．型指定の最適化
型指定をしなかったり、variantを使ったりすると、遅くなります。
doubleも遅くなるので極力使わない。

No.1 回答者： よろずやkinchan 回答日時： 2018/10/19 22:15

①ファイルを開く

②必要な列を別シートにコピー(ここで並び替え)
③①と②をファイル数繰り返す
⑤一つのシートにまとめる

一つのシートにまとめてから並べ替えではないのですか？