エクセルマクロVBAで、特定文字列を含む列を別シートへ抽出するプログラムを紹介します。
なお、特定の文字列は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｜列番号として使用する変数kを設定
- 2.8 プログラム7｜最終行の行番号をcmaxとして設定
- 2.9 プログラム8｜変数設定
- 2.10 プログラム9｜対象データを列ごとに処理
- 2.11 プログラム10｜各列の範囲を取得
- 2.12 プログラム11｜プログラム2のキーワードを全て取得
- 2.13 プログラム12｜各行にキーワードを含むセルがあれば
- 2.14 プログラム13｜キーワードを含む列を抽出用シートへ出力
- 2.15 プログラム14｜プログラム終了
- 3 Excel VBAについて詳しく理解したいなら
エクセルマクロVBAで複数の特定文字列を含む列を別シートへ抽出
ここでは、以下のデータ一覧を事例にして複数の特定文字列(の内、1つだけでも)を含む列を別シートへ抽出するVBAプログラムを紹介します。
このプログラムを実行すると、以下のように特定文字列を含む行のみを別シートへ書き出します。
ここでは対象文字列を「ID」と「取引金額」の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 |
'プログラム0｜変数宣言の指定 Option Explicit 'プログラム1｜プログラム開始 Sub GetColumnsWithKeywords() 'プログラム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｜列番号として使用する変数kを設定 Dim k As Long k = 1 'プログラム7｜最終行の行番号をcmaxとして設定 Dim cmax As Long cmax = ws1.UsedRange.Rows.Count 'プログラム8｜変数設定 Dim rng As Range Dim keyword As Variant 'プログラム9｜対象データを列ごとに処理 Dim i As Long For i = 1 To ws1.UsedRange.Columns.Count 'プログラム10｜全ての列を列ごとに取得 Set rng = ws1.Range("A1:A" & cmax).Offset(0, i - 1) 'Set rng = ws1.Range(cells(1, i), cells(cmax, i)) Debug.Print rng.Address 'プログラム11｜プログラム2のキーワードを全て取得 For Each keyword In Split(keywords, ",") 'プログラム12｜各列にキーワードを含むセルがあれば If Not rng.Find(keyword) Is Nothing Then 'プログラム13｜キーワードを含む列を抽出用シートへ出力 ws1.Columns(i).Copy (ws2.Columns(k)) k = k + 1 Exit For End If Next Next 'プログラム14｜プログラム終了 End Sub |
以下で詳しく説明します。
プログラム0｜変数宣言の指定
|
1 |
Option Explicit |
「Option Explicit」とは、変数宣言を強制するためのものです。
予期しないエラーを防止できるため「Option Explicit」を入力することを習慣化することを推奨しています。
詳しい説明は以下のページで紹介しています。
プログラム1｜プログラム開始
|
1 |
Sub GetColumnsWithKeywords() |
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()」で入力用のメッセージボックスを表示させます。
ここに削除したい文言を入力します。
削除対象の文言が複数ある場合は、以下のように「,」でつなぎます。
これで「ID」と「取引金額」の文字列のうち、どちらか一方の文字列を含んでいる列を抽出していきます。
なおメッセージボックスについては、以下のページでまとめています。
プログラム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｜列番号として使用する変数kを設定
|
1 2 |
Dim k As Long k = 1 |
プログラム13で、抽出先のシート(ws2)に列データを書き出していきます。
このデータの書き出しを行うとき、列番号を指定するのですが、その初期値を1としています。
k=1としているのは、A列(列番号1)から書き出しを行うためです。
プログラム7｜最終行の行番号をcmaxとして設定
|
1 2 |
Dim cmax As Long cmax = ws1.UsedRange.Rows.Count |
変数cmaxをws1の最終行の行番号を取得する変数として設定します。
上記のws1の最終行を取得できます。
ただし「対象シート.UsedRange」で取得できるセル範囲は、対象シートで[Ctrl] + [End]のショートカットキーを実行したときに選択されるセルとなります。
この事例ではデータは16行目までしか入っていませんが、[Ctrl] + [End]のショートカットキーでE18を選択しています。
よって18行目まで処理を行うことになります。
Debug.Printで検証してみます。
Debug.Printでの検証結果
|
1 2 3 |
Debug.Print ws1.UsedRange.Rows.Count >>>18 |
プログラム8｜変数設定
|
1 2 |
Dim rng As Range Dim keyword As Variant |
変数を設定しています。
変数については、以下のページで紹介しています。
プログラム9｜対象データを列ごとに処理
|
1 2 3 4 |
Dim i As Long For i = 1 To ws1.UsedRange.Columns.Count '(中略) Next |
対象データのシート(ws1)を列ごとに処理していきます。
上記のws1の最終列を取得できます。
ただし「対象シート.UsedRange」で取得できるセル範囲は、対象シートで[Ctrl] + [End]のショートカットキーを実行したときに選択されるセルとなります。
この事例ではデータは16行目までしか入っていませんが、[Ctrl] + [End]のショートカットキーでE18を選択しています。
よって18行目まで処理を行うことになります。
Debug.Printで検証してみます。
Debug.Printでの検証結果
|
1 2 3 |
Debug.Print ws1.UsedRange.Columns.Count >>>6 |
UsedRangeの仕様上、6列目(F列)まで取得します。このときF列は空欄ですが、VBAの処理は実行されます。
しかし大きな影響はないため、特に問題なしとして進めています。
プログラム10｜各列の範囲を取得
|
1 |
Set rng = ws1.Range("A1:A" & cmax).Offset(0, i - 1) |
対象データのシート(ws1)の各列の範囲をrngとして取得します。
以下のように記述しても同じ処理を実行できます
Rangeとoffsetを使った場合と、RangeとCellsを使った場合がありますが、どちらでも処理は実行されるので馴染みの方法を選択すればよいです。
プログラム11｜プログラム2のキーワードを全て取得
|
1 2 3 |
For Each keyword In Split(keywords, ",") '(中略) Next |
プログラム2で入力したkeywordsを「,」で区切って、繰り返し処理を行います。
ここでは「keywords=ID,取引金額」なので、Debug.Printで検証すると以下のようになります。
Debug.Printでの検証結果
|
1 2 3 4 5 6 |
For Each keyword In Split(keywords, ",") Debug.Print keyword Next >>>ID >>>取引金額 |
このようにプログラム2で入力したkeywordsを「,」で区切って、繰り返し処理を行うことができます。
なお「aaa,bbb,ccc」のように3つの文字列はもちろん、「,」でつなげば大量の文字列を対象にできます。
プログラム12｜各行にキーワードを含むセルがあれば
|
1 2 3 4 5 6 7 |
If Not rng.Find(keyword) Is Nothing Then 'プログラム13｜キーワードを含む列を抽出用シートへ出力 ws1.Columns(i).Copy (ws2.Columns(k)) k = k + 1 Exit For End If |
プログラム10で設定したrng(各行のセルデータ)に対して、プログラム2で入力したキーワードが含まれているかどうかをチェックします。
これで「各行データ(rng)に、対象文字列(keyword)が含まれていないことがなければ」という意味です。
NotとNothingを使っているため、二重否定になっているため、対象文字列(keyword)のセルが存在すれば、プログラム13が実行されます。
対象文字列の完全一致か部分一致か？
このプログラムでは、対象文字列と完全一致するセルが存在すれば、その列を別シートへ抽出します。
If Not rng.Find(keyword) Is Nothing Then
しかし部分一致(セル内の対象文字列が含まれている)でも、その列を別シートへ抽出したい場合もあります。
その場合は以下のように記述します。
If Not rng.Find(keyword, Lookat:=xlPart) Is Nothing Then
上記のとおり、「Lookat:=xlPart」を追加で記述します。これで部分一致も対象になります。
作成したいプログラムによって、完全一致と部分一致を使い分けると、作成できるプログラムの幅が広がります。
プログラム13｜キーワードを含む列を抽出用シートへ出力
|
1 2 3 |
ws1.Columns(i).Copy (ws2.Columns(k)) k = k + 1 Exit For |
対象データのシート(ws1)のi列目を抽出用シート(ws2)のk列目に書き出し(Copy)します。
抽出用シートの列番号kをカウントアップ
kはプログラム6で初期値1としていますが、ここでカウントアップさせるようにしています。
このカウントアップがないと、常に抽出用シート(ws2)の1列目(A列)にデータが書き出されてしまいます。
そのため、ここでカウントアップをしておきます。
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勉強に役立つ内容を紹介しています。
興味がある人はご覧ください。