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 コメントが含まれているセル
定数 xlCellTypeConstants 2 定数が含まれているセル
数式 xlCellTypeFormulas -4123 数式が含まれているセル
空白セル xlCellTypeBlanks 4 空白セル
最後のセル xlCellTypeLastCell 11 使われたセル範囲内の最後のセル
可視セル xlCellTypeVisible 12 すべての可視セル
条件付き書式 xlCellTypeAllFormatConditions -4172 表示形式が設定されているセル
条件付き書式(同じ条件付き) xlCellTypeSameFormatConditions -4173 同じ表示形式が設定されているセル
データの入力規則 xlCellTypeAllValidation -4174 条件の設定が含まれているセル
データの入力規則(同じ入力規則) xlCellTypeSameValidation -4175 同じ条件の設定が含まれているセル

 

引数Value(XlSpecialCellsValue)

選択オプションの項目 名前 説明
数値 xlNumbers 1 数値のあるセル
文字 xlTextValues 2 テキストのあるセル
理論値 xlLogical 4 理論値のあるセル
エラー値 xlErrors 16 エラーのあるセル

 

SpecialCellsの利用コード

 

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

コメント(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の独習には動画学習という方法もあります。 目と耳両方を使って学習することでさらに勉強効率を上げることもできます。

Udemy1eyecatch エクセルVBA初級者がUdemyで動画学習する講座おすすめ5選と無料講座の上手な使い方。

 

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

vbastudyeyecatch002 エクセルVBAを独学で習得する!ために大切な7つのポイントを解説します

 

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

vbaborder1eyecatchVBAでセルに罫線を設定する 位置・線種・太さ・色vbarowseyecatchVBA Rowsプロパティの範囲選択と関連のメソッドvbadeleteeyecatchVBA DeleteとClearの使い分け 削除とクリアの違いを最速理解vbacurrentregioneyecatchCurrentRegionは範囲取得プロパティ 連続データをまとめて掴むSpecialCellseyecatchSpecialCellsメソッドは特定のセルを選択する多機能ツールVBAFunctioneyecatchFunctionプロシージャーの働きと使い方 ユーザー定義関数もここ知れOK!vbacalleyecatchCallステートメントはプロシージャーの部品化に必須項目 エクセルVBA最速理解vbacharactereyecatchaCharactersプロパティで文字列中の一部の文字の書式設定や加工をするvbafontsyseyecatcha文字の書式の設定方法 Fontプロパティで装飾操作ができるVBAFormateyecatchFormat関数を書式設定のテッパン関数にする!実務での書式と重要5例vbaintfixeyecatch数値の整数部分を完全分離 Int・Fix・Abs・Sign関数で出来る使い方vbarndeyecatchRnd関数で乱数を取得。使えない戻り値を加工して利用度を上げるVBAコードvbaroundupdowneyecatchRoundUpとRoundDown関数の使い方。数値の切り方に注意が必要ですvbaroundeyecatchRound関数の毒針。VBAで数値を四捨五入する時の注意点vbamaxminaveeyecatchMax・Min・Averageワークシート関数で最大値・最小値・平均を求めるvbainsteeyecatchInstr関数・InstrRev関数は文字列を検索する。ファイルパス取得に威力vbareplaceeyecatchReplace関数は文字列を置換・削除する利用度の高い関数vbaselectcaseeyecatchSelect Case 条件分岐の使い方。ステートメントの基本はコレ。VBAValeyecatchVal関数の使い方はデータ型不一致の解決策。値を文字列型から数値型へVBAStringeyecatchString関数の使い方は文字を指定した数だけ並べることが出来る

 

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