エクセルマクロVBAで、複数の特定文字列を1つでも含む行を別シートへ抽出するプログラムを紹介します。
なお、特定の文字列が1つの場合でも対応できるプログラムとなっています。
目次
- 1 エクセルマクロVBAで複数の特定文字列を含む行を別シートへ抽出
- 2 VBAプログラムの解説
- 2.1 プログラム0｜変数宣言の指定
- 2.2 プログラム1｜プログラム開始
- 2.3 プログラム2｜キーワードを入力
- 2.4 プログラム3｜キーワードがない場合、プログラムを終了
- 2.5 プログラム4｜シート設定
- 2.6 プログラム5｜抽出データ出力用のシート追加
- 2.7 プログラム6｜抽出先のシートの初期値を設定
- 2.8 プログラム7｜変数設定
- 2.9 プログラム8｜対象データを行ごとに処理
- 2.10 プログラム9｜1行目(ヘッダー)を抽出先のシートへ出力(コピー)
- 2.11 プログラム10｜2行目以降を行ごとに取得
- 2.12 プログラム11｜プログラム2のキーワードを全て取得
- 2.13 プログラム12｜各行にキーワードを含むセルがあれば
- 2.14 プログラム13｜キーワードを含む行を抽出用シートへ出力
- 2.15 プログラム14｜プログラム終了
- 3 Excel VBAについて詳しく理解したいなら
エクセルマクロVBAで複数の特定文字列を含む行を別シートへ抽出
ここでは、以下のデータ一覧を事例にして複数の特定文字列(の内、1つだけでも)を含む行を自動抽出するVBAプログラムを紹介します。
このプログラムを実行すると、以下のように特定文字列を含む行のみを別シートへ書き出します。
ここでは対象文字列を「愛知」と「商品売却」の2つのキーワードとしています。
このキーワードもVBAプログラムの中で任意のキーワードを入力できるようにします。
本ページで紹介するVBAプログラムを実行すると、以下のように別シートへ抽出することができます。
それでは以下で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 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 |
'プログラム0｜変数宣言の指定 Option Explicit 'プログラム1｜プログラム開始 Sub GetRowsWithKeywords() 'プログラム2｜キーワードを入力 Dim keywords As String keywords = InputBox("抽出の対象となる文字列を記入。複数ある場合は、「,」で区分けすること") 'プログラム3｜キーワードがない場合、プログラムを終了 If keywords = "" Then: Exit Sub 'プログラム4｜シート設定 Dim ws1 As Worksheet Set ws1 = Worksheets("Sheet1") 'プログラム5｜抽出データ出力用のシート追加 Dim ws2 As Worksheet Set ws2 = Worksheets.Add(after:=ws1) ws2.Name = "NewSheet" 'プログラム6｜抽出先のシートの初期値を設定 Dim k As Long k = 2 'プログラム7｜変数設定 Dim rng As Range Dim keyword As Variant 'プログラム8｜対象データを行ごとに処理 Dim i As Long For i = 1 To ws1.UsedRange.Rows.Count 'プログラム9｜1行目(ヘッダー)を抽出先のシートへ出力(コピー) If i = 1 Then ws1.Rows(1).Copy (ws2.Rows(1)) End If 'プログラム10｜2行目以降を行ごとに取得 If i >= 2 Then Set rng = ws1.UsedRange.Rows(i) 'プログラム11｜プログラム2のキーワードを全て取得 For Each keyword In Split(keywords, ",") 'プログラム12｜各行にキーワードを含むセルがあれば If Not rng.Find(keyword, Lookat:=xlPart) Is Nothing Then 'プログラム13｜キーワードを含む行を抽出用シートへ出力 ws1.Rows(i).Copy (ws2.Rows(k)) k = k + 1 Exit For End If Next End If Next 'プログラム14｜プログラム終了 End Sub |
以下で詳しく説明します。
プログラム0｜変数宣言の指定
|
1 |
Option Explicit |
「Option Explicit」とは、変数宣言を強制するためのものです。
予期しないエラーを防止できるため「Option Explicit」を入力することを習慣化することを推奨しています。
詳しい説明は以下のページで紹介しています。
プログラム1｜プログラム開始
|
1 |
Sub GetRowsWithKeywords() |
VBAではプロシージャという単位でプログラムを呼び出します。
プロシージャの構文は下記となっています。
|
1 2 3 |
Sub プロシージャ名() '実行プログラム End Sub |
「Sub」で始まり「End Sub」で終わります。
プロシージャに関連するmoduleの話については以下で説明しています。
プログラム2｜キーワードを入力
|
1 2 |
Dim keywords As String keywords = InputBox("抽出の対象となる文字列を記入。複数ある場合は、「,」で区分けすること") |
変数keywordsを文字数型で設定します。
変数については、以下のページで紹介しています。
入力用のメッセージボックスに文字列を入力
|
2 |
keywords = InputBox("抽出の対象となる文字列を記入。複数ある場合は、「,」で区分けすること") |
「InputBox()」で入力用のメッセージボックスを表示させます。
ここに削除したい文言を入力します。
削除対象の文言が複数ある場合は、以下のように「,」でつなぎます。
これで「愛知」と「商品売却」の文字列を取得し、これらのうち一方の文字列を含んでいる行を削除していきます。
メッセージボックスについては、以下のページでまとめています。
プログラム3｜キーワードがない場合、プログラムを終了
|
1 |
If keywords = "" Then: Exit Sub |
プログラム2の入力値がない場合、「Exit Sub」でプログラムを途中終了させます。
プログラム4｜シート設定
|
1 2 |
Dim ws1 As Worksheet Set ws1 = Worksheets("Sheet1") |
対象のデータが含まれるシート(Sheet1)をws1として扱います。
プログラム5｜抽出データ出力用のシート追加
|
1 2 3 |
Dim ws2 As Worksheet Set ws2 = Worksheets.Add(after:=ws1) ws2.Name = "NewSheet" |
抽出データ出力用のシート作成して、ws2として扱うようにします。
そしてws2のシート名をNewSheetとします。
プログラム6｜抽出先のシートの初期値を設定
|
1 2 |
Dim k As Long k = 2 |
プログラム9~プログラム14で、抽出先のシート(ws2)にデータを書き出していきます。
このデータの書き出しを行うとき、行番号を指定しており、その初期値を2としています。
k=1としないのは、1行目はヘッダー情報を書き出すからです。
k=1は固定で書き出しを行うため、初期値として設定する必要はありません。
プログラム7｜変数設定
|
1 2 |
Dim rng As Range Dim keyword As Variant |
変数を設定しています。
変数については、以下のページで紹介しています。
プログラム8｜対象データを行ごとに処理
|
1 2 3 4 |
Dim i As Long For i = 1 To ws1.UsedRange.Rows.Count '(中略) Next |
対象データのシート(ws1)を行ごとに処理していきます。
上記のws1の最終行を取得できます。
ただし「対象シート.UsedRange」で取得できるセル範囲は、対象シートで[Ctrl] + [End]のショートカットキーを実行したときに選択されるセルとなります。
この事例ではデータは16行目までしか入っていませんが、[Ctrl] + [End]のショートカットキーでE18を選択しています。
よって18行目まで処理を行うことになります。
Debug.Printで検証してみます。
Debug.Printでの検証結果
|
1 2 3 |
Debug.Print ws1.UsedRange.Rows.Count >>>18 |
17,18行目は空欄ですが、VBAの処理は実行されます。
しかし大きな影響はないため、特に問題なしとして進めています。
プログラム9｜1行目(ヘッダー)を抽出先のシートへ出力(コピー)
|
1 2 3 |
If i = 1 Then ws1.Rows(1).Copy (ws2.Rows(1)) End If |
対象データのシート(ws1)の1行目を抽出用シート(ws2)の1行目にコピーします。
プログラム10｜2行目以降を行ごとに取得
|
1 2 3 4 |
If i >= 2 Then Set rng = ws1.UsedRange.Rows(i) '(中略) End If |
対象データシートの2行目以降のデータを1行ずつ取得していきます。
取得した行データはプログラム12でキーワードを含むかどうかのチェックに使用します。
プログラム11｜プログラム2のキーワードを全て取得
|
1 2 3 |
For Each keyword In Split(keywords, ",") '(中略) Next |
プログラム2で入力したkeywordsを「,」で区切って、繰り返し処理を行います。
ここでは「keywords=愛知,商品売却」なので、Debug.Printで検証すると以下のようになります。
Debug.Printでの検証結果
|
1 2 3 4 5 6 |
For Each keyword In Split(keywords, ",") Debug.Print keyword Next >>>愛知 >>>商品売却 |
このようにプログラム2で入力したkeywordsを「,」で区切って、繰り返し処理を行うことができます。
なお「aaa,bbb,ccc」のように3つの文字列はもちろん、「,」でつなげば大量の文字列を対象にできます。
プログラム12｜各行にキーワードを含むセルがあれば
|
1 2 3 4 5 6 7 |
If Not rng.Find(keyword, Lookat:=xlPart) Is Nothing Then 'プログラム13｜キーワードを含む行を抽出用シートへ出力 'ws1.Rows(i).Copy (ws2.Rows(k)) 'k = k + 1 'Exit For End If |
プログラム10で設定したrng(各行のセルデータ)に対して、プログラム2で入力したキーワードが含まれているかどうかをチェックします。
これで「各行データ(rng)に、対象文字列(keyword)が含まれていないことがなければ」という意味です。
NotとNothinを使っているため、二重否定になっているため、対象文字列(keyword)が含まれていれば、If文内のプログラム13とプログラム14が実行されます。
プログラム13｜キーワードを含む行を抽出用シートへ出力
|
1 2 3 |
ws1.Rows(i).Copy (ws2.Rows(k)) k = k + 1 Exit For |
対象データのシート(ws1)のi行目を抽出用シート(ws2)のk行目に書き出し(Copy)します。
抽出用シートの行番号kをカウントアップ
kはプログラム6で初期値2としていますが、ここでカウントアップさせるようにしています。
このカウントアップがないと、常に抽出用シート(ws2)の2行目にデータが書き出されてしまいます。
そのため、ここでカウントアップをしておきます。
Exit Forでプログラム11のForEachから抜ける
ここではk=k+1のカウントアップ後に、「Exit For」でプログラム11のループを抜けます。
プログラムの仕様上、各行で対象の文字列が1つ以上存在していれば、その行を抽出するようにしています。
そのため、ここではExit Forを抜けるようにしています。
もし複数の対象の文字列を全て含んでいる行のみを抽出するのであれば、プログラム12~プログラム13を書き換える必要があります。
プログラム14｜プログラム終了
|
1 |
End Sub |
プログラム1と対になるプログラムで、プログラム終了させるための記述です。
「End Sub」を読み込むと、プログラムが終了します。
プログラムの解説はここまでです。
Excel VBAについて詳しく理解したいなら
VBAを活用すると、仕事を効率化できる幅を広げることができます。
たとえば私が実際にVBAを活用して効率化してきた作業は以下の記事で紹介しています。
興味がある人は以下の記事もご覧ください。
動画でも解説しています。
エクセルマクロVBAで出来ることを15の事例で紹介｜日常業務をラクにするヒントを見つけよう！
(音声が小さいので、ボリュームを上げてご覧いただければと思います)
VBAの勉強方法
私はプログラミング初心者からVBAを勉強を始めて少しずつレベルアップしていきました。
少しずつレベルアップしながら、難しい内容に挑戦していくと効率的に学ぶことができます。
上記のリンクでは、VBA勉強に役立つ内容を紹介しています。
興味がある人はご覧ください。