VBAのRange.SpecialCellsメソッドについて

特定のセルを取得したい

「エラーが出てるセルをチェックしたい！」

「数式が入っているセルはどこだったかしら・・・」

「コメントがついているセルはどこだ！？」

そんなとき、SpecialCellsメソッドの出番です。

今回は、Rangeオブジェクトの中でもいぶし銀の活躍を見せる、SpcecialCellsメソッドについて見ていきたいと思います。

彼、SpcecialCellsメソッドを使えば、セル範囲の中から特定の条件に合致するセルだけを得ることができるという、地味ながら、便利な彼を見ていきましょう。

何はなくともオブジェクトブラウザー

まずは、オブジェクトブラウザーで彼がどのように書かれているかチェックしてみましょう！！

Rangeクラスのメンバーであることがわかりますね。

例えば、

Dim r as Range
set r= Range("A1:E1").SpecialCells(xlCellTypeFormulas)

この例の場合、変数rに格納されるのは、A1:E1のセルの中から数式だけが入ったRangeオブジェクトになります。

つまり、数式が入ったセルだけが取得できるのです。

第一引数

第一引数は、XCellType型の変数です。

お決まりのオブジェクトブラウザー

10個の値が用意されています。

それぞれの値がどういう意味かは、 公式HPに記載がございますので割愛いたします。

そして、残念ながらそれぞれの条件を組み合わせることはできません。

例えば、数式が入っているものと、定数が入っているものの両方の条件に合致するセルを得たい、ということはできません。

そういうときは、複数のセル範囲の集合を返してくれる、ApplicationのUnionメソッドを使います。

 

Sub SpecialCellsUnion()
   Dim formula As Range
   Dim constant As Range
   Dim uni As Range
   Set formula = Cells.SpecialCells(xlCellTypeFormulas)
   Set constant = Cells.SpecialCells(xlCellTypeConstants)
   Set uni = Application.Union(formula, constant)

  Debug.print uni.Address

End Sub

こうすることで、or条件で抽出することができます。

ちなみに、Cells.SpecialCellsとすることで、全てのセルを対象としています。

また、「コメントがついていて、かつ空白のセル」などAnd条件で検索したい場合は、Application.Intersectを使って、重複しているセル範囲を得ることで実現できます。

 

Sub SpecialCellsTestIntersect()
   Dim blank As Range
   Dim comment As Range
   Dim inter As Range
   Set blank = Cells.SpecialCells(xlCellTypeBlanks)
   Set comment = Cells.SpecialCells(xlCellTypeComments)
   Set inter = Application.intersect(blank, comment)
   Debug.Print inter.Address
End Sub

これらを利用すれば、結構スマートなコードが書ける気がしますね。

こういう組み合わせで地味に効果を発揮できるのが、このメソッドのいいところだと個人的に思っています。

第二引数

第二引数は、オプションです。

第一引数にxlCellTypeConstants（定数）、または、xlCellTypeFormulas（数式）を設定した場合に、さらに条件を絞り込むことができます。

こちらは、「＋」で複数の値を組み合わせることができます。

指定できる値は公式HPをご参照ください。

第二引数を使うとエラーのあるセルなど、かなり地味ながら役に立ちそうな感じの機能を提供してくれます。

プログラムのスピードアップ対策に！！・・・なるか？

では具体的にどんな For each でセル範囲を走査する際に地味に走査するセル範囲を絞り込むことができます。

例えば、次のコードはA1～E100のセルを走査するコードです。

 

Sub SpecialCellsForEach1()
   Dim rng As Range
   Dim targetRng As Range
   Set targetRng = Range("A1:E100")
   For Each rng In targetRng
      '(何かしらの処理)
   Next rng
End Sub

For Each を使ってA1～E100のセルを参照していますが、ケースによっては「文字列が入っているセルしか参照する必要がない」というときは、targetRngのSpecialCellsを呼び出すことであらかじめセルを絞った参照が可能です。

 

Set targetRng = Range("A1:E100").SpecialCells(xlCellTypeConstants)

こうすることで、参照するセルが絞られるので、処理速度が向上します。

もし、For Eachでセルを参照しているけど、どうもプログラムのスピードが遅い・・・という時はこのテクニックを使ってみるといいかも知れません。

既存のコードにほとんど手を入れる必要がないことが最大のメリットですね。

でも、セルを参照する処理の速度の向上目的であれば、セルの情報をVariant型の配列に代入してしまうのが一番効果的なんですがね・・・。

ここでは、最小限のコード改変で効果を出す、ということで捉えてください。

該当のセルが見つからない場合はエラーを返す

ここで一つだけ注意が必要です。

SpecialCellsメソッドは、該当するセルがない場合はエラーを返してきます。

そのため、ちゃんとした(？)プログラムの中で使う場合は、エラー処理が必須になります。

 

Sub SpecialCellsErr()
   Dim rng As Range
   Dim targetRng As Range
   On Error GoTo ERR_HANDLE

   Set targetRng = Range("A1:E100").SpecialCells(xlCellTypeConstants)
   For Each rng In targetRng
      '(何かしらの処理)
   Next rng

ERR_HANDLE:
   '(エラー処理)
End Sub

これは「条件を選択してジャンプ」機能です

便利なメソッドですが、実はこの機能はここにあります。

選択できる項目もほぼ、VBAと同じです。

また、マクロの記録で「条件を選択してジャンプ」をするとSpecialCellsメソッドが記録されます。

終わりに

地味ながら、強力なセルの抽出機能を持ったSpecialCellsメソッド。

もっとスマートなコードを書きたい、既存のコードへ手を加えずに処理速度を上げたい、といったちょっとした要求に応えてくれること請け合いです。

そのいぶし銀な魅力を伝えたるため、記事を書いた次第です。

少しでもこの記事が呼んでくれた方の役に立つことを願って・・・。

ここまでお読みいただき、ありがとうございました。