SpecialCellsメソッドはセル検索し選択する

SpecialCellseyecatch

エクセルVBAで、条件に合うセルの複数選択が必要になることは多いです。
「簡単そうで面倒なコード設計」になりがちな問題を、SpecialCellsメソッドで簡単に解決することが出来ます。
是非使い方を覚えておきましょう。

こんにちは じゅんぱ店長 (@junpa33) です。

エクセルシート上で作業するときに、複数のセルをまとめて選択したい場合が頻繁にあります。

特に、「ある特定の条件」に当てはまる「セル」だけを取り出したい時はどうでしょうか?

最初のうちは、ひとつづつ「セルの内容」を確認して、条件に当てはまるセルを選び出し、

『Ctrlキー』を押しながらポチポチッとクリックしていたのではないでしょうか。

エクセルシート上の基本機能として、

「条件を選択してジャンプ...」という機能があります。

これだと条件を指定すれば、一度に必要セルを選択することが出来ます。

今回はこの「条件を選択してジャンプ...」というエクセルシート上の機能を、

ExcelVBAマクロではどのように記述すれば実現できるのかを説明します。

SpecialCellsメソッドとは

エクセルVBAには、セルへの処理作業について非常に便利に働く、SpecialCellsというメソッドがあります。

エクセルシートのリボン上にも装備された機能のVBA版ですが、是非このSpecialCellsメソッドをVBAで使えるようになっておきましょう。

エクセルの「条件を選択してジャンプ」という機能

エクセルに備わっている「条件を選択してジャンプ...」という機能はどういったものかというと

vbaspecialcellsp001

エクセルのリボンの「検索と選択」からのメニューで「条件を選択してジャンプ...」をクリックします。

選択オプションウインドウが開きます。

vbaspecialcellsp002

この選択オプションにある項目を「選択条件」としてセルを複数同時に選択することが出来ます。

VBAにはSpecialCellsというコードがある

この選択オプションウインドウはあくまでも、エクセルシート上でカーソルとマウスで行う操作です。

VisualBasicEditor(VBE)上でこの機能をVBAコードとして利用するには、

VBAのコードとして「SpecialCells」というものが準備されています。

SpecialCellsメソッドというものです。

このSpecialCellsメソッドを使えば、求める条件のあるセルをまとめて選択することが出来ます。

SpecialCellsメソッドで特定のセルを選択する

vbaspesialcellsp020

SpecialCellsメソッドは、「特定のセルを選択する」という機能を持っています。

その上で、Rangeオブジェクトのプロパティを利用すれば、さらに色々な処理を行うことが出来ます。

VBAのSpecialCellsで利用できる項目は、この黄色マーカーの8項目になります。

vbaspecialcellsp003a

SpecialCellsの構文

構文(記述方法)

  • オブジェクト .Specialcells ( Type[ , Value ] )

[ ] 内は省略可能です。

 オブジェクトの部分で、選択範囲を指定します。Rangeオブジェクトです。

 引数Typeの部分で、検索取得条件となる定数(XlCellType )を指定します。引数Valueは、条件の副項目として必要な場合に指定します。

 戻り値としてはRangeオブジェクトになります。

引数Type(XlCellType )

選択オプションの項目名前説明
コメントxlCellTypeComments-4144コメントが含まれているセル
定数xlCellTypeConstants2定数が含まれているセル
数式xlCellTypeFormulas-4123数式が含まれているセル
空白セルxlCellTypeBlanks4空白セル
最後のセルxlCellTypeLastCell11使われたセル範囲内の最後のセル
可視セルxlCellTypeVisible12すべての可視セル
条件付き書式xlCellTypeAllFormatConditions-4172表示形式が設定されているセル
条件付き書式(同じ条件付き)xlCellTypeSameFormatConditions-4173同じ表示形式が設定されているセル
データの入力規則xlCellTypeAllValidation-4174条件の設定が含まれているセル
データの入力規則(同じ入力規則)xlCellTypeSameValidation-4175同じ条件の設定が含まれているセル

引数Value(XlSpecialCellsValue)

選択オプションの項目名前説明
数値xlNumbers1数値のあるセル
文字xlTextValues2テキストのあるセル
理論値xlLogical4理論値のあるセル
エラー値xlErrors16エラーのあるセル

SpecialCellsの利用コード

引数Typeのそれぞれの定数項目を設定してみます。

MEMO

セルの背景色についてはこちらを参考にしてください。

vbacellspaintbackeyecatch「塗りつぶし」背景色をVBAで記述する

コメント(xlCellTypeComments)

コメントが含まれているセルを緑色にします。

vbaspecialcellsp004

コード
Sub SpecialCellsコメント()
    Range("B2:G10").SpecialCells(xlCellTypeComments).Select
    Selection.Interior.ColorIndex = 10
End Sub

定数に値を指定しても同じ結果が得られます。

Range("B2:G10").SpecialCells(-4144).Select

定数(xlCellTypeConstants)

定数が含まれているセルを黄色にします。数式で計算された値のあるセルは選択されません。

この定数(xlCellTypeConstants)指定に対しては引数Valueを設定することが出来ます。

vbaspecialcellsp005

コード
Sub SpecialCells定数a()
    Range("B2:G10").SpecialCells(xlCellTypeConstants).Select
    Selection.Interior.ColorIndex = 6
End Sub

定数に値を指定しても同じ結果が得られます。

Range("B2:G10").SpecialCells(2).Select

数式(xlCellTypeFormulas)

数式が含まれているセルを水色にします。「数値」「文字」「理論値」「エラー値」すべてが選択されます。

この定数(xlCellTypeFormulas)指定に対しては引数Valueを設定することが出来ます。

vbaspecialcellsp006

コード
Sub SpecialCells数式a()
    Range("B2:G10").SpecialCells(xlCellTypeFormulas).Select
    Selection.Interior.ColorIndex = 8
End Sub

定数に値を指定しても同じ結果が得られます。

Range("B2:G10").SpecialCells(-4123).Select

引数Valueを使う場合

  1.  数値 xlNumbers
    引数Valueで「数値 xlNumbers」を指定するとセルの値が数値のものが選択されます。
    vbaspecialcellsp007
    Sub SpecialCells数式数値a()
        Range("B2:G10").SpecialCells(xlCellTypeFormulas, xlNumbers).Select
        Selection.Interior.ColorIndex = 8
    End Sub
    

    定数に値を指定しても同じ結果が得られます。

    Range("B2:G10").SpecialCells(-4123, 1).Select
    
  2. 文字 xlTextValues
    引数Valueで「文字 xlTextValues」を指定するとセルの値が文字のものが選択されます。
    vbaspecialcellsp008
    Sub SpecialCells数式文字a()
        Range("B2:G10").SpecialCells(xlCellTypeFormulas, xlTextValues).Select
        Selection.Interior.ColorIndex = 8
    End Sub
    

    定数に値を指定しても同じ結果が得られます。

    Range("B2:G10").SpecialCells(-4123, 2).Select
    
  3. 理論値 xlLogical
    引数Valueで「理論値 xlLogical」を指定するとセルの値が理論値のものが選択されます。
    vbaspecialcellsp009
    Sub SpecialCells数式理論値a()
        Range("B2:G10").SpecialCells(xlCellTypeFormulas, xlLogical).Select
        Selection.Interior.ColorIndex = 8
    End Sub
    

    定数に値を指定しても同じ結果が得られます。

    Range("B2:G10").SpecialCells(-4123, 4).Select
    
  4. エラー値 xlErrors
    引数Valueで「エラー値 xlErrors」を指定するとセルの値がエラー値のものが選択されます。
    vbaspecialcellsp010
    Sub SpecialCells数式エラー値a()
        Range("B2:G10").SpecialCells(xlCellTypeFormulas, xlErrors).Select
        Selection.Interior.ColorIndex = 8
    End Sub
    

    定数に値を指定しても同じ結果が得られます。

    Range("B2:G10").SpecialCells(-4123, 16).Select
    
  5. 定数を複数指定 数値とエラー値
    引数Valueで複数の定数を指定することが出来ます。「数値 xlNumbers」と「エラー値 xlErrors」を指定します。
    複数を同時指定するには「+」で定数をつなぎます。
    vbaspecialcellsp011
    Sub SpecialCells数式複数指定a()
        Range("B2:G10").SpecialCells(xlCellTypeFormulas, xlNumbers + xlErrors).Select
        Selection.Interior.ColorIndex = 8
    End Sub
    

    定数に値を指定しても同じ結果が得られます。

    Range("B2:G10").SpecialCells(-4123, 1 + 16).Select
    

空白セル(xlCellTypeBlanks)

空白のセルを灰色にします。空に見えるセルでも「空白文字」が入っている場合は選択されません。

vbaspecialcellsp012

コード
Sub SpecialCells空白セルa()
    Range("B2:G10").SpecialCells(xlCellTypeBlanks).Select
    Selection.Interior.ColorIndex = 15
End Sub

定数に値を指定しても同じ結果が得られます。

Range("B2:G10").SpecialCells(4).Select

最後のセル(xlCellTypeLastCell)

最後のセルをローズ色にします。

vbaspecialcellsp013

コード
Sub SpecialCells最後のセルa()
    Range("B2:G10").SpecialCells(xlCellTypeLastCell).Select
    Selection.Interior.ColorIndex = 38
End Sub

定数に値を指定しても同じ結果が得られます。

Range("B2:G10").SpecialCells(11).Select

可視セル(xlCellTypeVisible)

可視セルをアイボリー色にします。
5行目とD列を非表示にした後、セル範囲内の可視のセル色をアイボリーにします。
そして、5行目とD列を表示(可視)に戻します。

vbaspecialcellsp014

コード
Sub SpecialCells可視セルa()
    Rows(5).Hidden = True
    Columns(4).Hidden = True
    Range("B2:G10").SpecialCells(xlCellTypeVisible).Select
    Selection.Interior.ColorIndex = 19
    Rows(5).Hidden = False
    Columns(4).Hidden = False
End Sub

定数に値を指定しても同じ結果が得られます。

Range("B2:G10").SpecialCells(12).Select

条件付き書式(xlCellTypeAllFormatConditions)

条件付き書式が設定されたセルを薄緑色にします。
1から10までの数値で5よりも大きい数値が太字表示される条件付き書式です。

vbaspecialcellsp015

コード
Sub SpecialCells条件付き書式a()
    Range("B2:G10").SpecialCells(xlCellTypeAllFormatConditions).Select
    Selection.Interior.ColorIndex = 35
End Sub

定数に値を指定しても同じ結果が得られます。

Range("B2:G10").SpecialCells(-4172).Select

データの入力規則(xlCellTypeAllValidation)

データの入力規則が設定されたセルをアイスブルー色にします。
データの入力規則は2022/4/1から20224/10までの日付入力のみ受け付けるという規則です。

vbaspecialcellsp016

コード
Sub SpecialCellsデータの入力規則a()
    Range("B2:G10").SpecialCells(xlCellTypeAllValidation).Select
    Selection.Interior.ColorIndex = 24
End Sub

定数に値を指定しても同じ結果が得られます。

Range("B2:G10").SpecialCells(-4174).Select

SpecialCellsの使用時のコードエラー

vbaspesialcellsp021

SpecialCellsメソッドは、検索条件に一致するセルがない場合はエラーとなります。

コード進行が鰓ストップするのを防止するために、エラー時の処理対応コードが必要になります。

vbaspecialcellsp017

引数Typeを「定数」引数Valueを「文字」に設定します。

コード
Sub SpecialCells定数文字a()
    Range("B2:G10").SpecialCells(xlCellTypeConstants, xlTextValues).Select
    Selection.Interior.ColorIndex = 3
End Sub

vbaspecialcellsp018

引数Typeを「定数」引数Valueを「数値」に設定します。

コード
Sub SpecialCells定数数値a()
    Range("B2:G10").SpecialCells(xlCellTypeConstants, xlNumbers).Select
    Selection.Interior.ColorIndex = 3
End Sub

実行時エラーが発生します。

vbaspecialcellsp019

「エラーを発生させるコード行を飛ばして(無視して)実行行を次に進める」とか「エラー発生時に別行にジャンプしてエラーメッセージを表示する」などの処理コードを埋め込んでおきましょう。

コード
Sub SpecialCells定数数値改()
    On Error Resume Next
    Range("B2:G10").SpecialCells(xlCellTypeConstants, xlNumbers).Select
    Selection.Interior.ColorIndex = 3
End Sub

SpecialCellsメソッドで特定のセル選択のまとめ

SpecialCellsメソッドは、かなり利用範囲が広く、応用の利くメソッドです。

是非とも使えるようになっておきましょう。

コレ!という時に非常に便利です。 ” 使い方の発想はあなた次第です! ”

「セルの特定の条件にどれも合わない場合はエラーストップする」ことには十分注意して、

エラーストップ回避コードはかならず設置しておきましょう。

エクセルVBAの独習には動画学習という方法もあります。 目と耳両方を使って学習することでさらに勉強効率を上げることもできます。

エクセルVBAを独習するのに参考書は欠かせません。 参考書選びは自分に合った「相棒」にできるものを選んでいきたいです。

今回の記事はここまでです。   最後までご覧いただき有難うございました。

「エクセルVBA最速理解」の記事一覧を開く

<記事内容についての告知>VBAコードの記述記事においては、その記述には細心の注意をしたつもりですが、掲載のVBAコードは動作を保証するものではりません。 あくまでVBAの情報の一例として掲載しています。 掲載のVBAコードのご使用は、自己責任でご判断ください。 万一データ破損等の損害が発生しても当方では責任は負いません。