【VBA】指定の範囲から特定の文字列を探す

Question

こちらの識者の方々にはいつもお世話になっています。
VBAの質問です。

環境は下記になります。
OS=windowsXP SP3
Office=Excel2003(11.8347.8403) SP3

A列に"aa"の文字列が含まれる場合、その行を非表示にして、印刷する。
"aa"の文字列が無かった場合は印刷しない。
というコードを書きたいのですが、分からず困っています。

Dim EndRow As Long
Dim i As Long
EndRow = cells(Rows.Count, 1).End(xlup).Row

For i = 1 To EndRow
　　If Cells(i, 1) = "aa" Then Rows(i).EntireRow.Hidden = 1
　Next
で一行ずつ調べていって非表示にすることはできましたが、その後がわかりません。

上記のような場合、どのようなコードが適していますでしょうか。
なお、上記For Next文は必ず使いたいというわけではありません。
質問に不備不足等ございましたらご指摘ください。
ご面倒お掛けしますがよろしくお願いします。

cj_mover · Accepted Answer

＃２、４、ｃｊです。度々すみません。追加レスです。
慌てて間違え（？）ました。

> A列に"aa"の文字列が含まれる場合
こういう場合は、Like 演算子を使うよりも、
InStr()関数を使う方がスムーズです。というより、そうするべきでした。

Sub Re8291447()
　　Dim rng As Range
　　Dim flg As Boolean

'　　flg = False
　　For Each rng In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
　　　　If InStr(rng.Value, "aa") > 0 Then
　　　　　　rng.EntireRow.Hidden = True
　　　　　　flg = True
'　　　　　　Exit For　　'　　"aa"にマッチするセルはひとつしかない、という前提なら、イキ。
　　　　End If
　　Next
　
　　If flg Then
　　　　ActiveSheet.PrintOut Preview:=True　　'　　一応、プレビューにしてますが、要らなきゃFalse、もしくは、トル。
　　Else
　　　　MsgBox "not found"
　　End If

Rows.Hidden = False
End Sub

cj_mover · Answer

＃２、４、５、７、ｃｊです。
＃７お礼欄、拝見しました。

> For EachとFor Nextについては、例えば「条件に合った行を削除する」といったような処理をする場合、EndRowから処理を開始していくわけですから、総当たりとはいえ For i = EndRow To 1 Step -1のような記述になるため、For Nextを使用し、ただ順番に全てのオブジェクトに対し同じ処理をすればいいような場合はFor Eachを使用したほうがよい。というような理解で合っていますでしょうか。

合っています。
私より説明や例示が解り易いです。

> 少しVBAに取り組んでみて、その人が書いたコードを何度も見返すのですが、未だに半分以上が何を書いているのかわからないものの、自分のVBAに対する理解が深まるごとにその人の凄さに気付く毎日で、「これは冗長なんじゃないか？前任者が組んだら、もっと可読性にすぐれ、かつ処理も早く、エラーが起きにくいコードが書けるんじゃないのか」と自分でコードを書きながら常々思っています。

いつかは後任に引き継ぐ、ということを念頭において書くようにすると、
また違ったものが見えてくるかも知れませんから、
コードに添えるコメントなど工夫するなどして、
今の経験を未来に活かしてあげて欲しいです。
取組み方は身に染みてよく解りますし、共感できます。
人それぞれ、ではありますが、
結論を急ぐでなく、理解した上で今書けるものを書いて、
続けているうちに、引き出しを増やしていって、
とか、少し抑え気味位の方が総合的にうまく行くのかも。
（＃これ↑は、自分への戒め）

それでは、また。

cj_mover · Answer

＃２、４、５、ｃｊです。
＃５お礼欄にてお尋ねの件お答えします。

■> ... like演算子ではなく、InStr関数を使った方がいい理由 ...■

これは＃５で私が書いた
#5> Like 演算子を使うよりも、
#5> InStr()関数を使う方がスムーズです。というより、そうするべきでした。
に関して、説明が足りなかったですね。すみません。

しかしながら、、
> ＞A列に"aa"の文字列が含まれる場合
> と書きましたが、”aa”の文字列の場合の誤りです。
ということがハッキリしたので、今の時点では、私の追加レス＃４、５は、
実は必要なくて、＃２で完結していた、ということなので、
Like 演算子よりもInStr()関数よりも、この場合は、= 演算子を使っている
＃２のやり方の方がベターだという結論に変わってしまうのですけれども。
さておき。

”そうするべきでした”は誤解を招きやすい表現でした。
これは、私自身の（2007年頃からの）発言の一貫性という意味で、
また、普段であれば私は、迷わずInStr()関数を使っている処でしたから、
「私ならばそうするべきでした」という意味で書いています。
Like 演算子でも、InStr()関数でも、どちらも正解になり得ます。

”Like 演算子を使うよりも、InStr()関数を使う方がスムーズです。”
これは、
「対象となる文字列の中に、特定の文字列が、含まれるかどうか」
を求めるという目的に限定しての話ですが、、、。
ざっくり言って、InStr()関数の方が処理が速い、ということです。
InStr()関数自体の本来の目的は
「対象となる文字列の中に、特定の文字列が、最初に見つかる位置」
を求めることですから、
目的に適う意味では、ワイルドカードを組み合わせたLike 演算子の方が
直接的で適切、という見方も出来るかも知れません。
ただ、InStr()関数の処理能が高いので、
「含まれるかどうか」を調べる手段としては、
一般的にも徐々に、InStr()関数がLike 演算子に取って代わるように
なって来ています。
今日的には、そして質問掲示板の回答例としては、
InStr()関数の方が既に多数派と言ってもいいように思います。
要するにパフォーマンスを重視した結果として、
歴史的な篩を掛けられた上で、
この目的では、Like 演算子が淘汰されつつある、といった感じです。
当然ながらLike 演算子に出来てInStr()関数には出来ない、ことも
ある訳ですから、目的、に合わせて考えないといけませんね。
偶々、本件ご質問の直前に、パターンマッチングに関する質問で
Like 演算子を使った回答をしたばかりだったので、
それに釣られて＃４で私は書き間違えてしまった、
間違いではないが最適解でもなかった、というような事情でした。

■> ... For iではなくFor Eachを使用した理由 ...■

これを正しく理解して貰うように書くことについては、
量的、時間的に限界があるようも思いますが（ホントは書ききれない）、
追々機会に触れていく内に自然に理解が深まっていくであろうこと
に期待しつつ、拙いながらも指標になるよう手短に書いてみます。

指定したオブジェクトを総当たりでループするなら、
For Each ... Next ループを使うのが一般的です。
質問タイトルの、”指定の範囲から”という言葉に素直に応えれば、
For Each ... Next ループになるだろうと考えましたので、選びました。
私が初めて手にしたＶＢＡの教則本では、セル範囲のループについて、
最初はFor ... Next ループを覚えるように書いてありましたが、
次のセクションではFor Each ... Next ループを優先するように
とも書いてありました。
一般論として、オブジェクトへのアクセス処理には相応の時間を要します。
　　For i = start To end
　　　　objects.Item(i).property = ...
　　Next i
この記述では、
ループの回数分だけ、objects（コレクション）を呼び直していることになります。
対して
　　For Each object In objects
　　　　object.property = ...
　　Next
objectsを一度だけ呼び出してコレクション内のobjectを参照しています。
For Each ... Next ループを使う方が速くて軽い、というケースが殆どです。
個人的な印象で違いを誇張して喩えるなら、
「大量の荷物を一個ずつ普通自動車で運ぶか、トラックで運ぶか」
「直接タクシーで帰る？最寄駅まで鉄道を使った方が速いのになぁ、、、」
といった感じです。
"... In objects"の部分が"最寄駅まで鉄道"に喩えられます。
（不慣れな土地で交通手段が判らない内は暫くタクシー使う人も居るでしょうけれど）
無論、条件によっては、鉄道を使わない方が速い場合もありますね。
For Each ... Next ループが適さない場面もあり、例外もありますが、
総当たりで一様な処理をするなら殆どの場面で
For Each ... Next ループが優ります。
ＶＢＡの教え方の歴史という意味では、InStr()関数の場合とは逆に、
ループの仕方による処理能の違いを意識的に説明することは
減ってきているような気もします。
標準的なＰＣのスペックが急速に高まり、以前ほど違いが出難くなった、
ということなのかも知れません。
簡潔に書けて読み易い、という理由でFor Each ... Next ループを
選ぶような説明の方が目立ってきているようです。
私が書くＶＢＡは殆ど他人に使って貰う前提なので、そういう意味では
遅いと知っていてそれを奨めたり書いたりするのは、
基本的に自分の中では許せなかったりします。
ひとつひとつは微かな違いであっても、待機時間の累積を考えると、
コストやユーザーのストレスに係ると思うからです。
他方、学ぶ意識の高い質問者さんに対しては、現状から
段階を一段進めるようにリードするような回答を心掛けたい、
とか思うこともあり、、、。
今回、For Each ... Next ループを持ち出したのは、
そういう動機の方が強かったりもします。

///
以上、説明のような、言い訳のような、、、でした。長すみません
参考になることあれば幸いです。

■■
一応、（裏付け？）資料としてテストサンプルを挙げておきます。
同じ処理をするのに、どの程度、処理速度に違いがあるか、
計時した結果をイミディエイトウィンドウに表示します。
（テストを実行するのはSub test8291447です）
（計時以外は何も出力しません。）

Sub test8291447()
　　Dim s As String
　　Dim t As Single
　　Dim 試行数 As Long
　　Dim i As Long
　　Dim flg As Boolean

' ' InStr()関数　と　Like 演算子　比較
　　試行数 = 10000000
　　s = "oooooooooooooooAAooooooooooooooo"

t = Timer
　　For i = 1 To 試行数
　　　　flg = InStr(s, "AA") > 0
　　Next i
　　t = Timer - t
　　Debug.Print "InStr関数", t

t = Timer
　　For i = 1 To 試行数
　　　　flg = s Like "*AA*"
　　Next i
　　t = Timer - t
　　Debug.Print "Like演算子", t

' ' For Each ... Next ループ　と　For ... Next ループ　比較
　　試行数 = 1000

t = Timer
　　For i = 1 To 試行数
　　　　ForEachNext
　　Next i
　　t = Timer - t
　　Debug.Print "ForEachNext", t

t = Timer
　　For i = 1 To 試行数
　　　　ForNext
　　Next i
　　t = Timer - t
　　Debug.Print "For...Next", t

End Sub

Sub ForNext()
　　Dim i As Long
　　Dim s As String

For i = 1 To 500
　　　　s = Rows(i).Address(0, 0)
　　Next i
End Sub

Sub ForEachNext()
　　Dim c As Range
　　Dim s As String

For Each c In Rows("1:500")
　　　　s = c.Address(0, 0)
　　Next
End Sub

tom04 · Answer

こんばんは！横からお邪魔します。＞A列に"aa"の文字列が含まれる場合・・・とありますが、コードを拝見すると、「含まれる」ではなく「aa」そのものの場合になっていますね。ループせずにオートフィルタを使ってみてはどうでしょうか？ Sub Sample1() Dim i As Long, myFlg As Boolean With ActiveSheet .Range("A1").AutoFilter field:=1, Criteria1:="<>aa" For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row If Rows(i).Hidden = True Then myFlg = True Exit For End If Next i If myFlg = True Then .PrintOut Else MsgBox "非表示行はありません。" End If End With End Sub ※　Sheetはオートフィルタがかかったままですので、最後はオートフィルタを手動で解除する必要があります。尚、余計なお世話かもしれませんが、「aa」が含まれる場合は＞Criteria1:="<>aa" を＞Criteria1:="<>*aa*" に変更してください。m(_ _)m

cj_mover · Answer

＃２、ｃｊです。

ごめんなさい。
> A列に"aa"の文字列が含まれる場合
でしたね。勘違いしてました。

Sub Re8291447b()
　　Dim rng As Range
　　Dim flg As Boolean

'　　flg = False
　　For Each rng In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
　　　　If rng.Value Like "*aa*" Then
　　　　　　rng.EntireRow.Hidden = True
　　　　　　flg = True
'　　　　　　Exit For　　'　　"aa"にマッチするセルはひとつしかない、という場合に、イキ。
　　　　End If
　　Next
　
　　If flg Then
　　　　ActiveSheet.PrintOut Preview:=True　　'　　一応、プレビューにしてますが、要らなきゃFalse、もしくは、トル。
　　Else
　　　　MsgBox "not found"
　　End If

Rows.Hidden = False
End Sub

失礼しました。

WindFaller · Answer

私は、ExcelもVBAも長い間遠ざかっているので、もしかしたら、間違いがあるかもしれません。

こういう場合、マクロにするかどうかは別として、オートフィルタを使うのが楽だと思います。ただし、以下のマクロの欠陥は、最初の1行目が該当する場合は、その部分だけの別のマクロを加えなくてはなりません。

'//
Sub TestMacro1()
　Dim rng As Range
　Const SFIND As String = "aa" '検索語
　With ActiveSheet
　　Set rng = .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
　　If .AutoFilterMode Then
　　　.AutoFilterMode = False
　　End If
　　If WorksheetFunction.CountIf(rng, SFIND) > 0 Then
　　　rng.AutoFilter Field:=1, Criteria1:="<>" & SFIND '検索語
　　　.PrintOut Preview:=True　'プレビューモード、実際は、PrintOut
　　Else
　　　MsgBox "対象の検索語は見つかりません。", vbExclamation
　　End If
　　.AutoFilterMode = False
　End With
End Sub
'//

なお、
>Rows(i).EntireRow.Hidden = 1

True の代わりに、数字を使う方法は、ワークシート上はよいのですが、VBAでは、やめたほうがよいです。長い間には、その方法は、致命的な失敗をすることがあります。標準的には、Boolean値のTrue を数値に変換すれば、-1 を返しますが、別に-1の値が、True ということではありません。

cj_mover · Answer

こんにちは。お邪魔します。

テーマとしては色々書き方があって、人数分の正解がありそうですね。
とりあえず、特別な条件がなければ、
普段から私が平易に書いているやり方で、、、。
For Each Next で指定したセル範囲を総当たりにして、
フラグを採ります。

Sub Re8291447()
　　Dim rng As Range
　　Dim flg As Boolean

'　　flg = False　　'　　初期値はFalseなので必要ではありませんが、明示的に書きたい場合、イキ。
　　For Each rng In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
　　　　If rng.Value = "aa" Then
　　　　　　rng.EntireRow.Hidden = True
　　　　　　flg = True
'　　　　　　Exit For　　'　　"aa"にマッチするセルはひとつしかない、という場合に、イキ。
　　　　End If
　　Next
　　
　　If flg Then
　　　　ActiveSheet.PrintOut Preview:=True　　'　　一応、プレビューにしてますが、要らなきゃFalse、もしくは、トル。
　　Else
　　　　MsgBox "not found"
　　End If

' ' 元に戻す！！俗に"お行儀"などと呼びますね。
　　Rows.Hidden = False
End Sub

.Hidden プロパティの書換えは結構遅いので、非表示にする行が大量ならば、
それなりに対策したものを書いてみようと思いますが、
まずは基本をしっかり覚えて欲しいと思います。
ご要望あれば、と。

以上です。

MSZ006 · Answer

実際に動かしていないのですが、たぶんこんな感じです。
---------

Dim EndRow As Long
Dim i As Long
Dim sw as Long

sw=0
EndRow = cells(Rows.Count, 1).End(xlup).Row

For i = 1 To EndRow
　　If Cells(i, 1) = "aa" Then 
　　　Rows(i).EntireRow.Hidden = 1
　　　sw=1
　　End If
　Next

If sw=1 then
　Activesheet.PrintOut
End If

--------
それから、
＞A列に"aa"の文字列が含まれる場合

例えば、「aabc」のような文字列の場合は非表示にしなくてもよいんでしょうか？
上記のコードだと純粋に「aa」と入っている時だけしか非表示になりません。

【VBA】指定の範囲から特定の文字列を探す

＃２、４、ｃｊです。

＃２、４、５、７、ｃｊです。

＃２、４、５、ｃｊです。

こんばんは！

＃２、ｃｊです。

私は、ExcelもVBAも長い間遠ざかっているので、もしかしたら、間違いがあるかもしれません。

こんにちは。

実際に動かしていないのですが、たぶんこんな感じです。

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング