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

SpecialCellseyecatch

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

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

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

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

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

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

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

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

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

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

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

SpecialCellsメソッドとは

vbaspecialcellspp020

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

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

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

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

vbaspecialcellsp001

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

矢印下001

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

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のそれぞれの定数項目を設定してみます。

参考記事

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

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

コメント（xlCellTypeComments）

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

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

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

VBA
Sub SpecialCellsコメント()
    Range("B2:G10").SpecialCells(-4144).Select
    Selection.Interior.ColorIndex = 10
End Sub
定数（xlCellTypeConstants）

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

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

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

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

VBA
Sub SpecialCells定数a()
    Range("B2:G10").SpecialCells(2).Select
    Selection.Interior.ColorIndex = 6
End Sub
数式（xlCellTypeFormulas）

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

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

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

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

VBA
Sub SpecialCells数式a()
    Range("B2:G10").SpecialCells(-4123).Select
    Selection.Interior.ColorIndex = 8
End Sub

引数Valueを使う場合

 数値　xlNumbers

引数Valueで「数値　xlNumbers」を指定するとセルの値が数値のものが選択されます。

vbaspecialcellsp007
VBA
Sub SpecialCells数式数値a()
    Range("B2:G10").SpecialCells(xlCellTypeFormulas, _
        xlNumbers).Select
    Selection.Interior.ColorIndex = 8
End Sub

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

VBA
Sub SpecialCells数式数値a()
    Range("B2:G10").SpecialCells(-4123, 1).Select
    Selection.Interior.ColorIndex = 8
End Sub

文字　xlTextValues

引数Valueで「文字　xlTextValues」を指定するとセルの値が文字のものが選択されます。

vbaspecialcellsp008
VBA
Sub SpecialCells数式文字a()
    Range("B2:G10").SpecialCells(xlCellTypeFormulas, _
        xlTextValues).Select
    Selection.Interior.ColorIndex = 8
End Sub

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

VBA
Sub SpecialCells数式文字a()
    Range("B2:G10").SpecialCells(-4123, 2).Select
    Selection.Interior.ColorIndex = 8
End Sub

理論値　xlLogical

引数Valueで「理論値　xlLogical」を指定するとセルの値が理論値のものが選択されます。

vbaspecialcellsp009
VBA
Sub SpecialCells数式理論値a()
    Range("B2:G10").SpecialCells(xlCellTypeFormulas, _
        xlLogical).Select
    Selection.Interior.ColorIndex = 8
End Sub

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

VBA
Sub SpecialCells数式理論値a()
Range("B2:G10").SpecialCells(-4123, 4).Select
    Selection.Interior.ColorIndex = 8
End Sub

エラー値　xlErrors

引数Valueで「エラー値　xlErrors」を指定するとセルの値がエラー値のものが選択されます。

vbaspecialcellsp010

VBA
Sub SpecialCells数式エラー値a()
    Range("B2:G10").SpecialCells(xlCellTypeFormulas, _
        xlErrors).Select
    Selection.Interior.ColorIndex = 8
End Sub

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

VBA
Sub SpecialCells数式エラー値a()
Range("B2:G10").SpecialCells(-4123, 16).Select
    Selection.Interior.ColorIndex = 8
End Sub

定数を複数指定　数値とエラー値

引数Valueで複数の定数を指定することが出来ます。「数値　xlNumbers」と「エラー値　xlErrors」を指定します。
複数を同時指定するには「＋」で定数をつなぎます。

vbaspecialcellsp011
VBA
Sub SpecialCells数式複数指定a()
    Range("B2:G10").SpecialCells(xlCellTypeFormulas, _
        xlNumbers + xlErrors).Select
    Selection.Interior.ColorIndex = 8
End Sub

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

VBA
Sub SpecialCells数式複数指定a()
    Range("B2:G10").SpecialCells(-4123, 1 + 16).Select
    Selection.Interior.ColorIndex = 8
End Sub
空白セル（xlCellTypeBlanks）

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

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

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

VBA
Sub SpecialCells空白セルa()
    Range("B2:G10").SpecialCells(4).Select
    Selection.Interior.ColorIndex = 15
End Sub
最後のセル（xlCellTypeLastCell）

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

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

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

VBA
Sub SpecialCells最後のセルa()
    Range("B2:G10").SpecialCells(11).Select
    Selection.Interior.ColorIndex = 38
End Sub
可視セル（xlCellTypeVisible）

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

vbaspecialcellsp014
VBA
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

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

VBA
Sub SpecialCells可視セルa()
    Rows(5).Hidden = True
    Columns(4).Hidden = True
    Range("B2:G10").SpecialCells(12).Select
    Selection.Interior.ColorIndex = 19
    Rows(5).Hidden = False
    Columns(4).Hidden = False
End Sub
条件付き書式（xlCellTypeAllFormatConditions）

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

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

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

VBA
Sub SpecialCells条件付き書式a()
    Range("B2:G10").SpecialCells(-4172).Select
    Selection.Interior.ColorIndex = 35
End Sub
データの入力規則（xlCellTypeAllValidation）

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

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

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

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

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

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

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

vbaspecialcellsp017

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

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

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

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

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

vbaspecialcellsp019

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

VBA
Sub SpecialCells定数数値改()
    On Error Resume Next
    Range("B2:G10").SpecialCells(xlCellTypeConstants, xlNumbers).Select
    Selection.Interior.ColorIndex = 3
End Sub
目次へ戻る

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

vbaspesialcellsp021

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

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

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

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

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

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

vbastudyeyecatch2 エクセルVBAの独習でおすすめ参考書を7冊選ぶ。良書との出会いは大切です

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

エクセルVBA最速理解で必要な知識を集めよう！

エクセルVBA業務ツールで日常の業務改善を行いましょう。

＜記事内容についての告知＞

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

目次へ戻る

アンケートでポイ活しよう!!

アンケートに答えれば答えるほど ”使える” ポイントがたまります。

NTTコム サーチ

af_banner01

Dstyle web

dstyleweb_logo
dstyle_320x50-min

前の記事

vbacharactereyecatcha
Charactersで文字列中の一部の文字を加工する

次の記事

CurrentRegionは連続データをまとめて掴む
vbacurrentregioneyecatch