VBA

【VBA高速化】特定の列削除を高速化するコード

更新日:

広告

エクセルVBAで縦列を削除する方法は簡単です。ただ、条件によって複数列になると時間がかかりますので、高速化する裏技を紹介します。

列削除のコード

A列削除のコードはこちら

Columns("A").Delete

ただし、条件をいれたり複数列を削除する場合は時間がかかります。

列削除する元シート(前提条件)

前提として、下記のような乱数の関数RAND()を少数点1桁で四捨五入した式"=ROUND(RAND(),1)"が100列10000行にびっしり入っているシートがあります。

元シート

ここから、1行目が"1"以外の列を削除し、左詰めする動作を検証します。

列削除の高速化コード

結論はこちら

Dim i As Integer, j As Integer
Dim col_array As Variant

Application.ScreenUpdating = False ' 描画を停止する
Application.Calculation = xlCalculationManual ' 手動計算にする

j = 1
With Sheets(1)
 For i = 1 To 100
  If .Cells(1, i) = 1 Then
   col_array = .Range(.Cells(1, i), .Cells(10000, i))
   .Range(.Cells(1, j), .Cells(10000, j)) = col_array
   j = j + 1
  End If
 Next i
 .Range(.Cells(1, j), .Cells(10000, 100)).Clear
End With

Application.Calculation = xlCalculationAutomatic ' 自動計算にする
Application.ScreenUpdating = True ' 描画を再開する

コードの説明

高速化のポイントは.deleteを使わず、必要列をコピーして不要列を一括で削除する、という処理方法です。

4、5行目
Application.ScreenUpdating = False ' 描画を停止する
Application.Calculation = xlCalculationManual ' 手動計算にする

はよくあるVBA高速化です。

10行目
If .Cells(1, i) = 1 Then 条件です。1行目が"1"ならばデータを残し、それ以外は列を削除する処理を想定しています。

11行目
col_array = .Range(.Cells(1, i), .Cells(10000, i)) はその列の1行目から10000行目までの仮配列”col_array”に代入しています。

12行目
.Range(.Cells(1, j), .Cells(10000, j)) = col_array は仮配列に格納したデータをj列に代入しています。j列は初期は7行目で1を代入されています。

13行目
j = j + 1 にてj列を+1しています。

16行目
.Range(.Cells(1, j), .Cells(10000, 100)).Clear で最後に不要列をクリアしています。

20行目、21行目
Application.Calculation = xlCalculationAutomatic ' 自動計算にする
Application.ScreenUpdating = True ' 描画を再開する

という高速化処理後に元の状態に戻すコードです。

列削除の遅いコード

最終結果は同じですが、遅いコードはこちら

Dim i As Integer

Application.ScreenUpdating = False ' 描画を停止する
Application.Calculation = xlCalculationManual ' 手動計算にする
With Sheets(1)
 For i = 100 To 1 Step -1
  If .Cells(1, i) <> 1 Then
   Columns(i).Delete
  End If
 Next i
End With

Application.Calculation = xlCalculationAutomatic ' 自動計算にする
Application.ScreenUpdating = True ' 描画を再開する

コードの説明

6行目
For i = 100 To 1 Step -1
ここのポイントはiが1から100まで順に進むのではなく、100から1つずつ遡っていくことです。そうしないと、9行目の行をdeleteしたときに1列ずつ残ってしまいます。

このように、iが1から100まで順に進むと判定されない列が半分発生してしまいます。ここでは、iは100から1へ戻るようにすれば解決できます。

8行目
If .Cells(1, i) <> 1 Then もし1行目が1以外なら…

9行目
Columns(i).Delete i列目を削除する

処理時間比較

それでは、実際に処理時間を比較してみましょう。下記結果は5回実行した平均処理時間です。

高速化コード
(列コピペ)
遅いコード
(.delete利用)
描画停止・手動計算有効
遅いコード
(.delete利用)
描画停止・手動計算なし
処理時間0.6秒14.7秒45.6秒
5回の実行速度の平均値

まとめ

ご覧のように1秒かからずに実行できる方法があるにも関わらず、コードの違いで無駄に待ち時間を過ごすのはもったいないですね。工夫して高速化しましょう。
「列」削除でなく、「行」削除の高速化VBAも同じ方法で、必要行を残して不要行を一括消去すれば高速化が可能になります。

広告

おすすめ記事

1

楽天モバイルにMNPしたのでレビューします。 楽天モバイルって? 楽天モバイルはドコモ、au、ソフトバンクと同じく独自回線を用いた携帯キャリアです。いわゆる格安SIM(MVNO)は携帯キャリアから通信 ...

2

みなさまは増え続けるID,パスワードをどのように管理しているでしょうか。 わたしは、パスワード管理ソフト「ロボフォーム」を利用しています。 パスワード管理ソフトとは インターネットの普及により、銀行や ...

-VBA
-, ,

Copyright© アルパカさんのもふもふした生活 , 2021 All Rights Reserved Powered by STINGER.