集計対象レコードに対して、各項目別に複数条件でデータを削除したい場合に使えるVBAサンプルコードをご紹介します。基幹システムなどの生データに対し、毎回決まったデータを削除して目的のデータを整形する場合などに使えます。
まずは本記事でご紹介する内容について、3分動画を用意しましたので宜しければご覧ください。
目次
VBAで複数の削除条件を1度で実行するサンプルファイル
まずは本記事で扱うサンプルファイルのワークシートについて説明します（下図）。ワークシートは「元データ」、「出力シート」、「削除クエリシート」の3つです。
「元データ」ワークシート
サンプルの処理対象レコードがあるワークシートです。データはすべて0～9のランダムに生成された整数です。レコード総数は、ちょうど10000件用意しました。項目数は、A～Hの8項目です。
「出力シート」ワークシート
後ほどご紹介するVBAサンプルコードで処理された結果を出力するワークシートになります。
「削除クエリシート」ワークシート
元レコードに対して、削除するデータの条件を指定するシートです。その見方について、もう少し詳しく説明した図を用意しましたのでご覧ください。
サンプルファイルには、14個の削除条件を入力してあります。つまり1条件は1行として見ます。
例えば、赤枠で囲った3行は、「Aが1のレコードを削除」、「Aが2のレコードを削除」、「Aが3のレコードを削除」という削除条件を指定した3つの削除条件になります。
また、青枠で囲った1行は、「Bが6、かつCが4、かつFが3のレコードを削除」というAND条件を指定した削除条件になります。
AND条件とOR条件の見方は、Accessクエリのデザインビューと同じにしています。 ただし、*（アスタリスク）やlike演算子、複数項目入力などには対応しておりませんので、各セルに1つの数値を入力してくださいね。
緑枠は、各行の削除条件に対し、AND条件を構成する要素がいくつあるかを、ワークシート関数COUNTで計算させたものです。例えば赤枠の各削除条件では1つ、青枠では3つの要素でAND条件を構成しているとみます。
VBAで複数の削除条件を1度で実行するサンプルコード
VBAサンプルコードをご紹介します。構造としては、メインプロシージャの他に、SubプロシージャとFunctionプロシージャが1つずつあります。下のコードを、ダウンロードしたサンプルファイルの新しいモジュールにコピーして実行してみてください。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 | Option Explicit Dim varOrg As Variant '元データ格納用配列 Dim varDlq As Variant '削除クエリデータ格納配列 Dim varOut() As Variant '出力データ格納用配列 Dim rngOut As Range '出力データ範囲 Sub main() Dim varAry As Variant '処理対象レコード格納用配列 Dim r As Long Dim c As Long Dim i As Long Call SetArrays '配列準備 '出力データ要素番号リセット i = 0 'すべての元データを繰り返し処理 For r = LBound(varOrg) To UBound(varOrg) '処理対象レコードを配列格納 varAry = Array(varOrg(r, 1), varOrg(r, 2), varOrg(r, 3), varOrg(r, 4), _ varOrg(r, 5), varOrg(r, 6), varOrg(r, 7), varOrg(r, 8)) If Not IsDelData(varAry) Then '処理対象レコードが削除ではない場合 i = i + 1 '出力データ格納配列の要素番号インクリメント '元データ（処理対象レコード）を出力データ格納用配列へ For c = 1 To UBound(varOrg, 2) varOut(i, c) = varAry(c - 1) Next End If Next rngOut = varOut End Sub '■配列初期設定 Private Sub SetArrays() Dim rngOrg As Range '元データ範囲 Dim rngDlq As Range '削除クエリデータ範囲 Dim lngRCnt As Long 'データ配列行要素数 Dim lngCCnt As Long 'データ配列列要素数 '●元データ格納用配列初期処理 With ORG.Range("A1").CurrentRegion Set rngOrg = .Resize(.Rows.Count - 1).Offset(1) End With varOrg = rngOrg.Value '●出力結果表示範囲クリア、出力データ格納用配列初期処理 With OUT Set rngOut = .Range(.Cells(2, 1), .Cells(rngOrg.Rows.Count + 1, rngOrg.Columns.Count)) End With rngOut.ClearContents '行・列要素数格納 lngRCnt = UBound(varOrg) lngCCnt = UBound(varOrg, 2) '出力データ格納用配列定義 ReDim varOut(1 To lngRCnt, 1 To lngCCnt) As Variant '●削除クエリデータ初期処理 With DLQ.Range("A1").CurrentRegion Set rngDlq = .Resize(.Rows.Count - 1).Offset(1) End With varDlq = rngDlq.Value End Sub '■受け取ったレコードが削除対象か否かを判定 '引数：1レコード分のデータ（配列）、戻り値：True>>削除するデータ、False>>削除しないデータ Private Function IsDelData(ByVal vData As Variant) As Boolean Dim i As Long '削除クエリレコード繰り返し処理用 Dim n As Integer 'And条件カウント用 Dim c As Long Dim intAndCnt As Integer '対象削除クエリレコードのAnd条件数格納用 'すべての削除クエリレコードを繰り返し処理 For i = 1 To UBound(varDlq) intAndCnt = varDlq(i, UBound(varDlq, 2)) '対象削除クエリレコードからAnd条件数取得 n = 0 '処理済And条件数カウントリセット For c = 1 To UBound(varDlq, 2) - 1 '列方向繰り返し処理 If Not IsEmpty(varDlq(i, c)) _ And varDlq(i, c) = vData(c - 1) Then '削除クエリレコード値がEmpty値でなく、 'かつ削除クエリ値がレコード値と一致した場合 n = n + 1 'And条件数カウントアップ If n = intAndCnt Then '最後のAnd条件まで、すべての条件で一致した場合 IsDelData = True '削除条件を満たすのでTrueを返す Exit Function End If End If Next Next End Function |
VBA実行結果の確認
VBAサンプルコードを実行した結果を順番に確認しますね。
OR条件指定
削除クエリシートの1行は、1つの削除条件になりますが、これからご紹介する条件の指定方法により、複数の削除条件を指定できます。今回の例では、いづれも3つの削除条件ですが、いくつでも考え方は同じです。
A列が1、または2、または3を含むレコードを削除
まずは、初めの3つの削除条件からです。図の右側に示した削除クエリシートの薄ピンクの部分です。元データにあったA列の1～3（赤枠）は、VBA実行後に出力シートに集計されたデータからは削除されているのが確認できます。
D列が4、または5、または6を含むレコードを削除
つぎは、D列が4、または5、または6を含むレコードの削除です。見方はA列の時と同様、VBA実行後の出力シートで確認すると、D列の4、5、6を含むレコードが削除されてますね。
H列が7、または8、または9を含むレコードを削除
H列が7、または8、または9を含むレコードを削除した結果です。これまでと同様に出力シート側には、7、8、9を含むレコードは存在しません。
AND条件指定
削除クエリシートの1行の中に、複数列に数値を指定すれば、AND条件として指定できます。
B列が6、かつC列が4、かつF列が3のレコードを削除
削除クエリシートの10個目の削除条件ですが、3つの列にそれぞれ数値を指定することで、AND条件でレコードを削除しています。下図の通り、VBA実行前後を比較すると、意図した通りのレコードが削除されているのが分かります。
E列が3、かつG列が0のレコードを削除
今度はE列が3、かつG列が0のレコードを削除しています。元データでは、「E列が3、かつG列が0」のデータ個数は198もありましたが、VBA実行後の出力データではちゃんと0個になります。
A列が9、かつB列が2、かつD列が0、かつH列が1のレコードを削除
次はAND条件要素が4つの場合です。元データでは、この条件を満たすレコードが23個見つかりました。ここでもやはり出力データでは、削除条件を満足するレコードが削除されています。
C列が3、かつE列が7のレコードを削除
13個目の削除条件です。C列が3、かつE列が7のレコードを削除します。元データ側には、もともと1つしかないレコードです。
出力データシートの確認では、先にE列の7をフィルタリングした状態で、C列の3を確認していますが、C列の3が削除されているのが確認できます。
A列が3、かつC列が1、かつH列が4のレコードを削除
最後の削除条件です。これも元データシートには1レコードしかないものです。このレコードは、A列が3なので既にOR条件で行った削除処理（3番目の削除条件）で、すべての「A列が3」のレコードが削除されていますので、当然出力シートの確認でも、「A列が3」のレコードはありません。
VBAで複数の削除条件を1度で実行するサンプルコードのアルゴリズム
今回のVBAサンプルコードは、3つのプロシージャで構成されていますが、各プロシージャの解説の前に、モジュール変数から解説をはじめます。
モジュール変数宣言
VBAコード2行目で、元データシート上の集計対象レコードのデータ範囲を格納する配列変数を宣言しています。またVBAコード3行目で、削除クエリシート上の削除条件データ範囲を格納する配列変数を宣言しています。
VBAコード5行目では、出力データ格納用配列変数を宣言しています。この配列変数varOutについては、コードの中で配列要素数を再定義して利用します。
mainプロシージャ
メインプロシージャでは、集計処理に使う配列の初期化後、すべての対象レコードに対して、繰り返し処理を実行します。処理概要は、1レコードずつ削除すべきレコードなのか、もしくは出力シートへ転記するレコードなのかを判定します。
以下、主なVBAコードについて解説します。
VBAコード8行目では、1レコード分のデータを配列に格納するための配列変数「varAry」をローカル変数として宣言しています。
VBAコード15行目は、サブルーチン「SetArrays」を呼び出しています。「SetArrays」については、後ほど詳しく解説しますが、機能としては、モジュール変数で宣言した配列変数の初期化を行います。
集計対象レコードから削除対象を除くレコードを抽出する
VBAコード21～35行目では、集計対象レコードから削除対象レコード以外のデータを抽出して、出力データに転記する処理をしています。
21行目： For r = LBound(varOrg) To UBound(varOrg)
繰り返し構文Forの初期値には、配列変数「varOrg」からLBound関数で求めた最小要素番号を指定しています。また繰り返し終了には、配列変数「varOrg」からUBound関数で求めた最大要素番号を指定しています。
24行目： varAry = Array(varOrg(r, 1), varOrg(r, 2), varOrg(r, 3), varOrg(r, 4), varOrg(r, 5), varOrg(r, 6), varOrg(r, 7), varOrg(r, 8))
ここでは、r番目のレコードの各項目データを配列変数「varAry」に格納します。ここで設定された配列データ「varAry」は、このあとのユーザ定義関数「IsDelData」の引数として渡されます。
Arry関数はVBA関数の1つで、引数で指定した要素からなる配列を作ります。下図で例を使って詳しく解説しましたのでご覧ください。
図は4番目（r=4）のレコードの各項目データを、配列に格納するイメージを表したものです。このあとにIsDelData関数へ引数として渡されます。
またここで注意したいのが、Array関数で設定した配列の要素番号です。Array関数による配列の要素番号は、かならず0から始まります。たとえば、この例（r=4）の場合、下図のように配列からデータを取り出す際の要素番号の指定には注意が必要です。
27行目：If Not IsDelData(varAry, varDlq) Then
ユーザ定義関数IsDelDataは、集計対象レコードが「削除するデータ(戻り値がTrue）」、もしくは「削除しないデータ（戻り値がFalse）」かを判定する関数です。
条件式は、IsDelDataの戻り値とは排反の場合にThen以降を実行する意味になります。すなわちIsDelDataの戻り値がFalseのとき（削除しないデータのとき）、Then以降を実行することになります。
28行目： i = i + 1
削除しないレコードを出力シートへ転記する一連の処理（VBAコード28～33行目）の始めの部分になります。ここではまず、出力シートへ転記するレコードの要素番号をカウントアップさせています。
31～33行目： For c = 1 To UBound(varOrg, 2) ～ Next
削除しない集計対象レコードを、出力シート上へ転記する処理です。転記元の集計対象レコードの各項目を1つずつ、出力先の対応する項目へ転記しています。ここでも注意したいのが、配列varOutと配列varAryの2次の要素番号を1つずらしている点です。
ずらす理由については、先ほどの説明どおりArray関数の作る配列の最初の要素番号は0であるため、2次要素の最初の番号が1であるvarOutに合わせるからです。
SetArraysサブプロシージャ
本サンプルコードは、配列を使う集計処理をしますので、いくつかの配列変数を使用しています。そこでこのSetArraysプロシージャには、それら配列変数の初期設定機能をまとめています。
元データシート上の集計対象レコード格納用配列「varOrg」初期化処理
VBAコード49～52行目では、元データシートのレコード範囲を、RisizeプロパティとCurrentRegionプロパティを使用し、配列変数「varOrg」に格納しています。
出力シートへ出力するレコードを格納する配列変数「varOut」初期化処理
VBAコード55～65行目では、出力するレコードを格納する配列変数「varOut」の初期設定をしています。
62行目：lngCCnt = UBound(varOrg, 2)
ここでは、UBound関数を使い配列変数「varOrg」（集計対象レコードを格納）の2次の最大要素数を求め、変数「lngCCnt」に代入しています。これにより変数「lngCCnt」には、集計対象レコードの列数が代入されます。
65行目： ReDim varOut(1 To lngRCnt, 1 To lngCCnt) As Variant
ここで配列変数「varOut」を再定義しています。ReDimは、配列変数要素を定義するためのステートメントです。
VBAコード5行目では、「Dim varOut() As Variant」として変数宣言しましたが、配列を定義する上で必要な要素については、未定義のままでした。その理由は、VBAコードの処理が進む中で要素数が初めて決まるケースだからです。
たとえば今回の場合の要素数は、集計対象のレコード数（行方向）や項目数（列方向）ですが、特にレコード数については処理する中で求められますので、（いつも決まったレコード数なら別ですが。。。）このような方式をとる理由です。
ただ配列変数としての宣言をはじめにしておかないとコンパイルエラーになりますので、VBAコード5行目の記述は必要になります。
削除クエリシートのデータ範囲を格納する配列変数「varDlq」の初期化処理
VBAコード69～73行目では、削除クエリシートの削除条件データ範囲を、RisizeプロパティとCurrentRegionプロパティを使用し、配列変数「varDlq」に格納しています。
削除条件データ範囲には、下図網掛け部の「AND条件数」も含まれていますが、VBAコードの中で処理したAND条件数をチェックするために利用します。
IsDelDataファンクションプロシージャ
処理する対象レコード1つ分のデータを配列で受け取り（引数）、指定されたすべての削除条件と照合します。そして受け取ったレコードが、削除するレコードか否かの結果（戻り値）をブール型で返します。
AND条件数の取り扱い
下図は、関数内で扱われているAND条件数について解説したものです。まずVBAコード83行目で、削除クエリシートの9列目（青網掛け）の値を代入するための変数「intAndCnt」を宣言します。
次にすべての削除条件に対し、引数で受けた処理対象レコードを照合させます。そこで、削除条件の数だけ繰り返し処理するFor文 （赤字）を使います。その繰り返し処理の始めに、AND条件数を格納する変数「intAndCnt」にAND条件数を代入します（VBAコード87行目）。
集計対象レコードと削除条件の比較判定処理
下図は、引数で渡された1つの集計対象レコードと1つの削除条件を比較判定処理をするコードについて示したものです。VBAコード90～100行目で、それぞれのレコードの列要素を比較しています。
VBAコード91行目のIf文では2つの条件を判定し、条件が満たされればThen以下を実行します。まず「Not IsEmpty(varDlq(i,c)」により、削除条件に値があること、かつ「varDlq(i,c) = vData(c-1)」で数値が一致することがThen以下を実行するための条件になります。
すべての削除条件を満たしたかの判定処理
ユーザ定義関数IsDelDataは、削除条件に指定されているすべての数値が、対象レコードの数値と一致した場合のみTrue（削除対象のレコード）を返します。
具体的には、VBAコード90行目の繰り返し処理の中で、要素同士が一致した個数を数えさせ、その個数が変数「intAndCnt」に格納されている数値と一致した場合、Trueを返すようにすればよいことになります。
上図は、そのアルゴリズムを詳しく示したものです。まずVBAコード87行目で、各削除条件のAND条件数を変数「intAndCnt」に代入します。
つづくVBAコード89行目では、処理の過程で要素同士が一致した数をカウントするための変数nをリセットします。そして、VBAコード90行目の要素同士の比較で一致した場合、VBAコード94～99行目が実行されます。
まずVBAコード94行目で、1組の要素同士が一致したことによるカウントアップを行います。次のVBAコード95行目では、そのカウント数nとintAndCntが一致するか否かの判定を行います。
そこでTrue(すべての要素同士が一致）の場合、VBAコード96行目によりIsDelData関数がTrueを返すことになります。VBAコード97行目は、これ以降の処理は不要ですので、「Exit Function」で処理を抜けています。
まとめ
集計対象レコードの各項目に対して、複数の削除条件を指定し、一度に削除するサンプルコードをご紹介しました。本サンプルコードを応用すれば、生データから毎回同じパターンのデータを削除する作業の効率化が図れると思います。
Excelのフィルタ機能を使うデータの削除作業は、データ数が数万行にもなると時間もそれなりにかかりますし、人による手作業ですのでオペレーションミスを考慮し、確認作業にも多くの時間を取られます。
その点、お決まりの削除パターンであれば、一度VBAを組むことでデータ整形の作業スピードと精度が格段にあがりますのでお勧めです。