サイト内検索

基本 行・レコード操作

VBAコードで不要な小計行を削除する

集計対象のレコードの合間にある不要な小計行を削除するサンプルコードです。人からもらったExcelシートのデータに、いくつかのグループ毎に集計された小計行を見かけることがあります。

データ整形をする場合、これらの小計行は不必要なため、フィルタリングなどのExcel機能を使って行削除しますよね。今回はその行削除を、VBAコードで実行したいと思います。

では、まずはサンプルファイルを用意してくださいね。今回は小計行を追加したサンプルシート「SampleSheet02」を使います。

小計行削除用Excelサンプルシート

それでは、サンプルシートの確認ができましたら、つぎのVBAコードを真似て書いてみてくださいね。

小計行を削除するVBAサンプルコード
VBAで小計行を削除する

書き終えたら実行してみてください。上手くいけば、小計行だけが削除されますので、実行結果はサンプルシート01と同じになります。

小計行削除VBAサンプルコード実行後
サンプルシート01と同じになった

それでは、いつものようにVBAコードの説明にいきますね。今回は、初登場のVBAコードもありますよ。

 

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演算子」、「ワイルドカード」の紹介もしました。

[スポンサーリンク]



サイト内検索

-基本, 行・レコード操作
-

Copyright© 集計用VBAサンプルコードをやさしく学ぼう , 2020 All Rights Reserved.