エクセルVBA/SpecialCellsで特定文字列のセルを選択

質問

いつもお世話様です。

Sub test()
ActiveSheet.Range("C1:C200").SpecialCells(xlCellTypeConstants).Select
End Sub

で検索範囲内の「定数」のセルをいっぺんに選択できますが、これを例えば、「AAA」という文字列のセルだけを一度に選択するにはどうすればいいでしょうか？
（また、あるいは「123」という数値のセルの場合には？）
よろしくお願いします。

回答No.2

papayuka · ベストアンサー

こんな感じでしょうか？

Sub test1()
Dim c, myTarget As Range
Dim fStr As String, fad As String
fStr = Application.InputBox("検索値？", "検索", Type:=2)
If fStr = "" Then Exit Sub
Set myTarget = Nothing
　With Selection
　　Set c = .Find(fStr, LookIn:=xlValues, LookAt:=xlWhole)
　　If Not c Is Nothing Then
　　　 fad = c.Address
　　　 Do
　　　　　If myTarget Is Nothing Then
　　　　　　 Set myTarget = c
　　　　　Else
　　　　　　 Set myTarget = Application.Union(c, myTarget)
　　　　　End If
　　　　　Set c = .FindNext(c)
　　　 Loop While Not c Is Nothing And c.Address <> fad
　　End If
　End With
If Not myTarget Is Nothing Then myTarget.Select
End Sub

但し、文字の 123 と 数値の 123 と計算結果の 123 は全て拾いますので、
型までチェックの必要があるならご自分で修正して下さい。

お礼

いっぺんには無理なのでやはりLoopでまわすことになるんですね、ありがとうございました。

ではxlCellTypeConstantsの引数（なんでしょうか？）についてですが、いろいろやってみました。

SpecialCells(xlCellTypeConstants)では、全ての定数（""含む）
SpecialCells(xlCellTypeConstants,1)では「数値」
SpecialCells(xlCellTypeConstants,2)では「文字列」（""含む）

という理解でよろしいでしょうか？
また、1,2の他にも引数（？）はありますか？
何度も質問してすみません。

回答No.9

Wendy02

質問主のmerlionXXさん、スレッド汚してすみません。
もう一度、お借りします。

papayuka様へ
簡単な実験をしてみました。
実験的なコードは、それを出していると長くなるので割愛しますが、私は、AdvancedFilter , Match関数を取り扱ってみました。AdvancedFilterには、Criteriaを最初に用意しました。

条件は、
A2:D50000までを、=RandBetween(1,300) という式を貼り付け、それを値コピーで、定数化させました。文字列:AAAの置き場所は、A15000,A30000,A35000,A65535,A65536 と５つ。
Interior.ColorIndex
で、そのたびに、パターンの色を変えるという方法にしました。
実験ごとに、色が変化しているかは、チェックしています。

Match関数との比較ですから、A列のみの検索とします。Match関数は、通常、１つしか検索しませんが、同じものがあると、一番最初に見つけたものを返す特性があります。それを利用しました。

また、FindTest側の以下の部分は、削除します。
* Columns("A:G").Clear
* Range("A65536:G65536").Value = "AAA"
また、
　With Range("A1:G65536")
の部分は、
　With Range("A1:A65536")

としました。

計測は、API関数の、timeGetTime を使用し、Debug.Print で計測を書いていきました。PCの特性もあるかもしれませんが、結果としては、ご覧のとおりです。

スピードの計測:　単位/1000 秒

ForEachTest 13763
ForEachTest 13554
ForEachTest 13827
ForEachTest 13814

FindTest 3633
FindTest 3471
FindTest 3441
FindTest 3448
'--------------------
FilterTest1 756
FilterTest1 690
FilterTest1 675
FilterTest1 699

ForMatchTest 48
ForMatchTest 46
ForMatchTest 47
ForMatchTest 47

以上は、ほんの一例です。
直接のRangeオブジェクトを扱えば、その分、遅くなります。Rangeオブジェクトのアクセス数が多ければ多いほど、遅くなっていきます。EmptyのRangeオブジェクトは、Find メソッドの検索対象にはなりませんので、前回速くなったわけです。Findは、広範囲に検索するときは便利で、その方法は否定はできません。

しかし、Excel VBAで行える検索の方法はいろいろあるかと思います。いろんな方法を探して、試してみるのがよいのではないでしょうか？VBAの原則的なことを守っていれば、どれがベストだとは言えません。

回答No.8

papayuka

Wendy02さん、たび重なる回答ありがとうございます。

> 私は、ないと思っていますが、

そうですか、、、
一括で処理出来るなら便利だなと思いましたが、、、

> そして、本当に優劣を問うのは、私は、そのコードの処理スピードにあるのではないかと考えています。
> また、定番のFindメソッドの、その遅さとか考えると、別の良い方法はないかということなのです。
> Findメソッドを避けるというのは、その点にあるわけなのですね。

確かに処理スピードは速い方が越した事は無いですよね。
ただ、Findメソッドってそんなに遅いでしょうか？
私は、Find が遅いと言うより、 For と Do のスピードの違いだと思っています。

物凄く極端な例なのであまり現実的では無いですが、下記では Find の方が速いです。

'-----------------------------------------------------------------
Sub ForEachTest()
　Dim rng As Range
　Dim c As Range

　Columns("A:G").Clear
　Range("A65536:G65536").Value = "AAA"
　Range("A1").Select

　Const mySearchWord As String = "AAA"
　For Each c In Range("A1:G65536")
　　If c.Value = mySearchWord Then
　　　If rng Is Nothing Then
　　　　Set rng = c
　　　　Else
　　　　Set rng = Union(c, rng)
　　　End If
　　End If
　Next c
　rng.Select
End Sub

'-----------------------------------------------------------------
Sub FindTest()
Dim c, myTarget As Range
Dim fStr As String, fad As String

Columns("A:G").Clear
Range("A65536:G65536").Value = "AAA"
Range("A1").Select

fStr = "AAA"
Set myTarget = Nothing
　With Range("A1:G65536")
　　Set c = .Find(fStr, LookIn:=xlValues, LookAt:=xlWhole)
　　If Not c Is Nothing Then
　　　 fad = c.Address
　　　 Do
　　　　　If myTarget Is Nothing Then
　　　　　　 Set myTarget = c
　　　　　Else
　　　　　　 Set myTarget = Application.Union(c, myTarget)
　　　　　End If
　　　　　Set c = .FindNext(c)
　　　 Loop While Not c Is Nothing And c.Address <> fad
　　End If
　End With
If Not myTarget Is Nothing Then myTarget.Select
End Sub
'-----------------------------------------------------------------

Wendy02さん
お付き合い頂きありがとうございました。
Collection については勉強になりました。
Wendy02さんの回答にケチを付けようとか、そう言った意図があった訳ではありませんので、お気を悪くされたとしたらスミマセン。m(__)m

merlionXX さん
長々とスレをお借りして、失礼致しました。

回答No.7

Wendy02

papayukaさん、検証ありがとうございます。

ちょっと気合を入れて掛からないと、オオボケしそうなので、昨日は書きませんでした。

＞選択（Select）以外でも、Collection 化したものに一括で処理を加える方法は無いと考えて良いでしょうか？

私は、ないと思っていますが、力量のある人は違う考えというか、もともと、Collectionなどを代用するなんていう、ルール違反はしないのかもしれません。以前、私は、今回書いたようなCollection の使い方は、間違っていると思っていたぐらいです。これは、コントロールを入れるものだと思い込んでいましたからね。確かに、CommandButtonでも、なんでも、不特定数のコントロールを溜めていくのは便利なんです。

＞test2 は検索結果が多いとエラーになりますので、test3 の方が良いと思います。

数が多くて、１列～２列なら、もともと、Excelの機能のフィルター系を使ったほう速いですね。

私がこういうコードを書くきっかけとして、Rangeオブジェクトの扱いの問題なのですね。私は、正直なところ、なるべく、実体のあるRangeオブジェクトをそのまま取り扱いたくないというのが、発想の原点なのです。だから、そのコード自体には、良し悪しはないと思います。

そして、本当に優劣を問うのは、私は、そのコードの処理スピードにあるのではないかと考えています。たかが、Excel VBAで、そのような話を言うのは、お笑い種だと思う方もいるかもしれませんが。

また、定番のFindメソッドの、その遅さとか考えると、別の良い方法はないかということなのです。Findメソッドを避けるというのは、その点にあるわけなのですね。

二次元配列でも何でも、配列で扱えるなら、ある程度の大きさ、ある程度の取り扱い数になったら、配列のほうが軍配があがる、と考えています。つまり、変数を、ワークシートのRangeオブジェクトから切り離してあげることで、格段にそのスピードがあがる、と思います。（ただし、Set myRange= Range(.....) は切りはなれていません。また、配列のVariant 型の格納は、数が増えるとうまくいかないことがあるようです。）

ワークシート関数のVlookup や Match と比較すると、そのスピードには、おそらく、100倍以上の違いがあるのではないでしょうか？だから、VBAでも、1列の場合は、Match をVBA掲示板で、多用されるわけなんですよね。そういう発想の元に、今回書いたものなのです。

回答No.6

papayuka

Wendy02さん
回答ありがとうございます。

> やはり、For Each ～ In MyCollection というループ以外の方法はないと思います。
>
> その先の作業によっては、ふさわしくないかもしれません。
> 確かに、その全てをSelect するだけなら、あまり意味がありません。

選択（Select）以外でも、Collection 化したものに一括で処理を加える方法は無いと考えて良いでしょうか？
例えば、myCollection.value = "BBB" とか、myCollection.Copy とか、、、
もし無いとすると、Excelの「セルについてだけを捕らえた場合」は、union で Rangeオブジェクトにセットした方が扱いやすそうですね。

あと、釈迦に説法ですが、、、
test2 は検索結果が多いとエラーになりますので、test3 の方が良いと思います。
それと、test2 、test3、ともに mySearchWord が無かった場合の処理が必要ですね。

回答No.5

Wendy02

merlionXXさん、下に２つほど標準的なものを作りましたので、それでご勘弁を。m(__)m

papayukaさん

＞該当セルを Collection 化 し、それをどのようにすると選択させられるのでしょうか？

やはり、For Each ～ In MyCollection というループ以外の方法はないと思います。

myCollectionは、一種の配列なのに、Redim で配列の確保の処理が要りません。もともと、Collectin は、オブジェクトの配列を確保する手続きが簡単で、私は、通常、クラスのインスタンスのコントロールを確保させるために使っています。

＞Collection の要素をループさせなければならないなら、Collection 化する意味が無いような、、、、

ループする必要はあるものの、前処理が少ないのと、Collection化した後のオブジェクトが独立しているので、ループさせても、それほど問題はないだろうと思っていました。

ただし、これはテクニックの１つであって、その先の作業によっては、ふさわしくないかもしれません。確かに、その全てをSelect するだけなら、あまり意味がありません。個々に後から処理をしたり、配列内で、削除したりすることも出来ますし、内部のオブジェクトの順序も変えらます。そういう点で便利なのです。そして、複数のオブジェクトを確保する別の方法です。

ただ、もしも、Selectを主眼として、一括選択するだけでしたら、String型で、そのアドレスを溜めていって、Range一本でまとめたほうが速いですからね。

例:
Sub test2()
Dim myStr As String
Dim c As Range
Const mySearchWord As String = "AAA"
For Each c In Range("C1:C200")
　If c.Value = mySearchWord Then
　　 myStr = myStr & "," & c.Address
　End If
Next c
　Range(Mid(myStr, 2)).Select
End Sub

それに、オブジェクトとして、以下のようにまとめちゃっても良いわけですしね。

Sub test3()
　Dim rng As Range
　Dim c As Range
　Const mySearchWord As String = "AAA"
　For Each c In Range("C1:C200")
　　If c.Value = mySearchWord Then
　　　If rng Is Nothing Then
　　　　Set rng = c
　　　　Else
　　　　Set rng = Union(c, rng)
　　　End If
　　End If
　Next c
　rng.Select
End Sub

こういうのにしてあれば、落ち着くのかな？（^^;

回答No.4

papayuka

#2です。

> また、1,2の他にも引数（？）はありますか？

既に回答が出てますが、VBE画面の表示－オブジェクトブラウザで specialcells で検索し XlSpecialCellsValue をクリックすると解りますね。

Wendy02さん
　# merlionXXさんがよろしければ、この場を借りて Wendy02さんに質問があるのですが、、、

私は Collection を使った事が無く、#3 で提示されている例が今回の質問に対してどのような意味を持つのか解りませんでした。

Collection のヘルプを見ると「Collection オブジェクトに含まれるすべてのメンバを取得するときには For Each ... Next ステートメントを使います。」とありました。
Range("C1:C200") を For Each ... Next でループさせた後に、Collection の要素をループさせなければならないなら、Collection 化する意味が無いような、、、、

該当セルを Collection 化 し、それをどのようにすると選択させられるのでしょうか？
myCollection.Select として見ましたがダメでした。(^^;;

宜しくお願いします。m(__)m

回答No.3

Wendy02

merlionXX さん、こんにちは。

＞また、1,2の他にも引数（？）はありますか？

一般操作側のジャンプのところをみていただければ分かりますが、残りは２つです。分からない場合は、ヘルプで確認してください。

xlNumbers　数値
xlTextValues　文字
xlLogical　論理値
xlErrors　エラー値

慣れないうちは、組み込み定数を使ったほうがよいですね。

＞「AAA」という文字列のセルだけを一度に選択する
次の作業をどうするかによって変わると思います。

Range("C1:C200")
をFindメソッドを使う方法もありますが、

以下のようにすることも可能ですね。

Sub CollectionRng()
Dim myCollection As New Collection
Dim c As Range
Const mySearchWord As String = "AAA"
For Each c In Range("C1:C200")
　If c.Value = mySearchWord Then
　　 myCollection.Add c
　End If
Next c
End Sub

他には、C列だけですから、アドレスを配列に入れていく方法もあるかと思います。(2000以上なら、String型のほうがよい）

お礼

ありがとうございました。

論理値とエラー値だったんですね。
いつもすみません。

回答No.1

BLUEPIXY

一度にうまく選択する方法は、ないんじゃないかと思います。
findとfindnextで該当セルを見つけて
range.select
例えば
Range("A1,A3,A7").select
みたいな感じでやるとか

お礼

特定の値のみを一編に検索＆選択は出来ないんですね。
わかりました。ありがとうございます。

ではxlCellTypeConstantsの引数（なんでしょうか？）についてですが、いろいろやってみました。

SpecialCells(xlCellTypeConstants)では、全ての定数（""含む）

SpecialCells(xlCellTypeConstants,1)では「数値」

SpecialCells(xlCellTypeConstants,2)では「文字列」（""含む）

という理解でよろしいでしょうか？
また、1,2の他にも引数（？）はありますか？

何度も質問してすみません。