集計対象のレコードの合間にある不要な小計行を削除するサンプルコードです。人からもらったExcelシートのデータに、いくつかのグループ毎に集計された小計行を見かけることがあります。
データ整形をする場合、これらの小計行は不必要なため、フィルタリングなどのExcel機能を使って行削除しますよね。今回はその行削除を、VBAコードで実行したいと思います。
では、まずはサンプルファイルを用意してくださいね。今回は小計行を追加したサンプルシート「SampleSheet02」を使います。
それでは、サンプルシートの確認ができましたら、つぎのVBAコードを真似て書いてみてくださいね。
書き終えたら実行してみてください。上手くいけば、小計行だけが削除されますので、実行結果はサンプルシート01と同じになります。
それでは、いつものようにVBAコードの説明にいきますね。今回は、初登場のVBAコードもありますよ。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | Option Explicit '小計欄を行削除する Sub Sample028() Dim r As Long '行ルーチン処理用変数 Dim lngERow As Long 'レコード最終行番号用変数 '2列目に入っているデータの最終行番号を取得 lngERow = Range("B" & Rows.Count).End(xlUp).Row '行削除なので最終行番号側からルーチン処理 For r = lngERow To 2 Step -1 '2列目のデータの最後の文字に「市場」の '2文字が含まれている場合にThen以降を実行 If Cells(r, 2) Like "?市場" Then '行削除実行 Cells(r, 2).EntireRow.Delete End If Next End Sub |
9行目:lngERow = Range("B" & Rows.Count).End(xlUp).Row
レコードの最終行を取得するVBAコードになります。ただ、今回は今までとはちょっと違います。実は、今回のExcelシートの最終行1048576の列が、これまでのA列ではなく、B列を指定している点が違います。
この理由については、下の図でお話ししますね。
上の2つの黄色セルを、はじめにセレクトするスタート地点とします。ここからVBAコード上で「.End(xlUp)」、キー操作では「Ctrl」+「↑」を実施します。すると、それぞれ緑色のセルにジャンプしますよね。
それら緑色セルの行番号は、Excelシートから読み取れますね。ExcelシートのA列、およびB列それぞれの最終行セル(黄色セル)を出発点とした場合の最終レコードのセル(緑色セル)の行番号は、それぞれ「20」と「21」になります。
もうお気づきかと思いますが、出発点の列が違うことにより、データ最終行の番号が違いますよね。ということは、データの最終行番号を取得する時は、今回の例のように、列の違いに気をつけないといけません。
列の取り方により、場合によってはレコード数が変わり、集計結果も違ってきてしまいますから。
今回の場合、小計行まできちんと削除する必要がありますので、「21」行目までがレコード範囲であることを、VBAコード上で取得しなくてはいけません。
という理由で、VBAコード9行目では、B列のExcelシート最終行からレコードの最終行番号を取得しています。
16~21行目:If Cells(r, 2) Like "?市場" Then ~ End If
削除する行を判別するためのIf文です。今回初登場のVBAコードがありますよね。「Like」は「Like演算子」といいます。この「Like」は、Likeを挟む前後の文字列を比較するのに使われます。
今回の例では、「Like」の前が、サンプルシート2列目のデータ(Cells(r, 2))になっていますね。そのCells(r, 2)と「Like」の後ろに続く"?市場”を比較して、条件が一致すれば「Then」以降を実行することになります。
ここで、Like演算子の右辺の文字列"?市場”ですが、"?”を含んでますよね。これはワイルドカードと呼ばれるものです。
ワイルドカードには「?」よりも、「*」をよく使いますので、下にこれら2つの使い方を整理して書いてみました。。
今回の例ですと、"A市場", "B市場", "C市場", "D市場", "E市場"のバリエーションしかありませんよね。いづれも「市場」の前は1文字分ですので、VBAコードでは"?市場”と書いて、1文字分のワイルドカード「?」を使っています。
以上より、削除すべき行を特定するアイディアとして、「挿入されたすべての小計行の2列目には、本来入力されるべき「果物名」ではなく「~市場」が入力されている」ということを考慮しました。
補足ですが、上のアイディア以外にも「挿入されたすべての小計行のA列は空欄である」のように考えますと、E, F, G列が空欄であることを条件に使ってもいいですよね。
また、ワイルドカードには「#」などもありますが、VBA集計業務には経験上「*」を中心に「?」を少し使っていたくらいですので、この2個のワイルドカードをまずは抑えておきましょう。
まとめ
今回は、VBA集計業務のデータ整形に不要な小計行の削除方法についてお話ししました。その中で「Like演算子」、「ワイルドカード」の紹介もしました。