大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAで、可視セルや空白セルなどの、条件に一致したセル範囲を取得する方法について、ご紹介します。使うVBA関数は、SpecialCellsです。SpecialCellsを使えば、条件付き書式やデータ入力規則などの、設定漏れを確認することができます。

はじめに

この記事では、条件に一致したセル範囲を取得する、SpecialCellsについて解説します。

SpecialCellsを使えば、可視セルや、空白セル、条件付き書式が設定されたセル範囲を、取得することができます。

条件付き書式や、データの入力規則の設定漏れの確認に使えます。

では、具体的なVBAコードを使って、解説していきます。

この記事で紹介すること

  • 条件に一致するセル範囲を取得

目次から見たい項目へ移動すると便利ですよ。

目次

SpecialCellsの使い方

条件に一致したセル範囲を取得する、SpecialCellsについて、まとめました。

こんな感じです。

'コメント
Cells.SpecialCells(xlCellTypeComments).Select
'定数
Cells.SpecialCells(xlCellTypeConstants).Select 'すべて
Cells.SpecialCells(xlCellTypeConstants, 1).Select '数値
Cells.SpecialCells(xlCellTypeConstants, 1).Select '文字
'数式
Cells.SpecialCells(xlCellTypeFormulas).Select 'すべて
Cells.SpecialCells(xlCellTypeFormulas, 1).Select '数値
Cells.SpecialCells(xlCellTypeFormulas, 2).Select '文字
Cells.SpecialCells(xlCellTypeFormulas, 16).Select 'エラー
'空白セル
Cells.SpecialCells(xlCellTypeBlanks).Select
'最後のセル
Cells.SpecialCells(xlCellTypeLastCell).Select
'可視セル
Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Select
'条件付き書式
Cells.SpecialCells(xlCellTypeAllFormatConditions).Select 'すべて
Range("A1").SpecialCells(xlCellTypeSameFormatConditions).Select '同じ条件
'データの入力規則
Cells.SpecialCells(xlCellTypeAllValidation).Select 'すべて
Range("A1").SpecialCells(xlCellTypeSameValidation).Select '同じ規則

SpecialCellsは、可視セルや空白セルだけでなく、条件付き書式が設定されているかや、エラーのセルはないか、などを確認するのにも使えます。

では、SpecialCellsの動きについて、VBAコードを実行してみます。

VBAで条件に一致するセル範囲を取得

条件に一致するセル範囲を取得するSpecialCellsは、次のセル範囲を取得できます。

  • コメント
  • 定数
  • 数式
  • 空白セル
  • 最後のセル
  • 可視セル
  • 条件付き書式
  • データの入力規則

という感じです。

では、一つずつ動きをみてみましょう。

コメントのセルを選択

コメントが入力されているセルを選択するには、『xlCellTypeComments』を入力します。

VBAコードは、次のようになります。

Sub TEST1()
    
    'コメントのセルを選択
    Cells.SpecialCells(xlCellTypeComments).Select
    
End Sub

セル範囲は設定せず、『Cells』だけでできます。

コメントを入力したセルを用意しました。

コメントをセルに入力

コメントを入力したセルを用意

では、実行します。

コメントのセルを選択

xlcelltypecommentsでコメントが入力されているセルを選択

コメントが入力されているセルを、選択できました。

定数のセルを選択

次は、定数が入力されているセルを選択する方法です。『xlCellTypeConstants』を入力します。

定数のセル選択では、次の値を区別して選択できます。

  • すべて
  • 数値のみ
  • 文字のみ

すべて

まずは、定数のすべてを選択してみます。

定数の全てを選択する、VBAコードは、次のようになります。

Sub TEST2()
    
    '定数のセルを選択
    Cells.SpecialCells(xlCellTypeConstants).Select
        
End Sub

セル範囲は設定せず、『Cells』だけでできます。

すべて定数で入力した表を用意しました。

すべて定数で入力したセルを用意

では、実行します。

xlCellTypeConstantsで定数が入力されているセルを選択

定数が入力されているセルを、選択できました。

すべて定数で入力しているので、表の全てが選択されています。

数値

次は、定数の内、数値を選択してみます。2番目の引数に『1』を入力します。

定数の数値を選択する、VBAコードは、次のようになります。

Sub TEST3()
    
    '定数で数値のセルを選択
    Cells.SpecialCells(xlCellTypeConstants, 1).Select
        
End Sub

セル範囲は設定せず、『Cells』だけでできます。

先ほどと同じ表を使います。

すべて定数で入力したセルを用意

では、実行します。

xlCellTypeConstantsで定数で数値が入力されているセルを選択

定数で数値が入力されているセルを、選択できました。

こんな感じで、数値だけの選択もできます。

文字

次は、定数の内、文字を選択してみます。2番目の引数に『2』を入力します。

定数の文字を選択する、VBAコードは、次のようになります。

Sub TEST4()
    
    '定数で文字のセルを選択
    Cells.SpecialCells(xlCellTypeConstants, 2).Select
        
End Sub

セル範囲は設定せず、『Cells』だけでできます。

先ほどと同じ表を使います。

すべて定数で入力したセルを用意

では、実行します。

xlCellTypeConstantsで定数で文字が入力されているセルを選択

定数で文字が入力されているセルを、選択できました。

文字だけの選択もできます。

数式のセルを選択

次は、数式が入力されているセルを選択する方法です。『xlCellTypeFormulas』を入力します。

数式のセル選択では、次の値を区別して選択できます。

  • すべて
  • 数値のみ
  • 文字のみ
  • エラー

すべて

まずは、数式のすべてを選択してみます。

数式の全てを選択する、VBAコードは、次のようになります。

Sub TEST5()
    
    '数式のセルを選択
    Cells.SpecialCells(xlCellTypeFormulas).Select
        
End Sub

セル範囲は設定せず、『Cells』だけでできます。

『E列とF列』に数式を入力した表を用意しました。

数式を入力した表を用意

では、実行します。

xlCellTypeFormulasで数式が入力されているセルを選択

数式が入力されているセルを、選択できました。

E列とF列に数式を入力しているので、E列とF列が選択されています。

数値

次は、数式の内、数値を選択してみます。2番目の引数に『1』を入力します。

数式の数値を選択する、VBAコードは次のようになります。

Sub TEST6()
    
    '数式で数値のセルを選択
    Cells.SpecialCells(xlCellTypeFormulas, 1).Select
    
End Sub

セル範囲は設定せず、『Cells』だけでできます。

先ほどと同じ表を使います。

数式を入力したセルを用意

では、実行します。

xlCellTypeFormulasで数式で数値が入力されているセルを選択

数式で数値が入力されているセルを、選択できました。

F列にも数式は入力されていますけども、結果が文字なので、選択されていません。

こんな感じで、数値だけの選択もできます。

文字

次は、数式の内、文字を選択してみます。2番目の引数に『2』を入力します。

定数の文字を選択する、VBAコードは、次のようになります。

Sub TEST7()
    
    '数式で文字のセルを選択
    Cells.SpecialCells(xlCellTypeFormulas, 2).Select
    
End Sub

セル範囲は設定せず、『Cells』だけでできます。

先ほどと同じ表を使います。

E列とF列に、数式が入力されています。

数式を入力したセルを用意

では、実行します。

xlCellTypeFormulasで数式で文字が入力されているセルを選択

数式で文字が入力されているセルを、選択できました。

E列も、数式が入力されていますけども、結果が数値なので、選択はされません。

こんな感じで、数式で文字の選択もできます。

エラー

次は、数式の内、エラーとなっているセルを選択してみます。2番目の引数に『16』を入力します。

数式のエラーを選択する、VBAコードは次のようになります。

Sub TEST8()
    
    '数式でエラーのセルを選択
    Cells.SpecialCells(xlCellTypeFormulas, 16).Select
        
End Sub

セル範囲は設定せず、『Cells』だけでできます。

先ほどと同じ表を使います。

数式を入力したセルを用意

では、実行します。

xlCellTypeFormulasで数式でエラーとなっているセルを選択

数式でエラーとなっているセルを、選択できました。

数式のエラー検索に使えます。

空白セルを選択

次は、空白セルを選択してみます。

空白セルの選択には、『xlCellTypeBlanks』を使います。

空白セルを選択する、VBAコードは次のようになります。

Sub TEST9()
    
    '空白のセルを選択
    Cells.SpecialCells(xlCellTypeBlanks).Select
        
End Sub

セル範囲は設定せず、『Cells』だけでできます。

表の一部を空白にした表を用意しました。

空白セルのある表

表の一部を空白にした表を用意

では、実行します。

空白セルを選択

xlCellTypeBlanksで空白セルを選択

空白セルを、選択できました。

最後のセルを選択

次は、最後のセルを選択してみます。

最後のセルの選択には、『xlCellTypeLastCell』を使います。

最後のセルを選択する、VBAコードは次のようになります。

Sub TEST10()
    
    '最後のセルを選択
    Cells.SpecialCells(xlCellTypeLastCell).Select
    
End Sub

セル範囲は設定せず、『Cells』だけでできます。

次のような表を用意しました。

使う表

表を用意

では、実行します。

最後のセルを選択

xlCellTypeLastCellで最後のセルを選択

最後のセルを、選択できました。

注意点

最後のセルを選択する際に、注意があります。

行の高さ変更や、書式の変更したセルでも、最後のセルとして認識してしまいます。

11行目で、行の高さを変更してみました。

行の高さを変更

11行目で行の高さを変更した表

同じVBAコードを実行してみます。

行の高さを変更したセルが選択される

xlCellTypeLastCellを使って最後のセルを選択

行の高さを変更したところのセルが、選択されました。

『xlCellTypeLastCell』を使う際の、注意でした。

可視セルを選択

次は、可視セルを選択してみます。

可視セルの選択には、『xlCellTypeVisible』を使います。

可視セルを選択する、VBAコードは次のようになります。

Sub TEST11()
    
    '可視セルを選択
    With Range("A1").CurrentRegion
        .SpecialCells(xlCellTypeVisible).Select
    End With
    
End Sub

可視セルを選択する際は、範囲を指定する必要があります。

なので、『Range("A1").CurrentRegion』で、表のセル範囲を指定しています。

表の一部を非表示にした表を用意しました。

非表示にした表

表の一部を非表示にした表を用意

では、実行します。

可視セルのみを選択

xlCellTypeVisibleで可視セルを選択

可視セルを、選択できました。

選択範囲が、とびとびになっていますね。

表示されているセル範囲だけが、選択されていることがわかります。

条件付き書式のセルを選択

次は、条件付き書式が設定されているセルを選択してみます。

条件付き書式のセル選択には、2種類あります。

  • すべての条件付き書式のセル
  • 同じ条件付き書式のセル

それぞれ、

  • xlCellTypeAllFormatConditions(すべて)
  • xlCellTypeSameFormatConditions(同じものだけ)

を使います。

すべての条件付き書式のセル

すべての条件付き書式のセルを選択する、VBAコードは次のようになります。

Sub TEST12()
    
    'すべての条件付き書式のセルを選択
    Cells.SpecialCells(xlCellTypeAllFormatConditions).Select
        
End Sub

セル範囲は設定せず、『Cells』だけでできます。

条件付き書式を設定した表を用意しました。

条件付き書式を設定した表を用意

では、実行します。

xlCellTypeAllFormatConditionsですべての条件付き書式のセルを選択

すべての条件付き書式のセルを、選択できました。

すべてのセルに条件付き書式が設定されているので、正しく選択できていることがわかります。

同じ条件付き書式のセル

次は、同じ条件付き書式が設定されているセルを選択してみます。

VBAコードは次のようになります。

Sub TEST13()
    
    '同じ条件付き書式のセルを選択
    Range("A1").SpecialCells(xlCellTypeSameFormatConditions).Select
    
End Sub

確認したいセルを『Range("A1")』で指定しています。

この『Range("A1")』に設定されている条件付き書式と、同じ条件付き書式が設定されているセル範囲を選択します。

複数の条件付きが設定された表を用意しました。

色別に、同じ条件付き書式を設定しています。

複数の条件付き書式を設定した表を用意

では、実行します。

xlCellTypeSameFormatConditionsで同じ条件付き書式のセルを選択

同じ条件付き書式のセルを、選択できました。

色別に同じ条件付き書式が設定されていて、同じ背景色のセルが選択されているので、正しく選択されています。

データの入力規則のセルを選択

次は、データの入力規則が設定されているセルを選択してみます。

データの入力規則のセル選択には、2種類あります。

・すべてのデータの入力規則のセル

・同じデータの入力規則のセル

それぞれ、

  • xlCellTypeAllValidation(すべて)
  • xlCellTypeSameValidation(同じものだけ)

を使います。

すべてのデータの入力規則のセル

データの入力規則が設定されている、すべてのセルを選択する、VBAコードは次のようになります。

Sub TEST14()
    
    'すべての、データの入力規則のセルを選択
    Cells.SpecialCells(xlCellTypeAllValidation).Select
    
End Sub

セル範囲は設定せず、『Cells』だけでできます。

データの入力規則を設定した表を用意しました。

『B列』にデータの入力規則が設定されています。

データの入力規則を設定した表を用意

では、実行します。

xlCellTypeAllValidationですべてのデータの入力規則のセルを選択

データの入力規則が設定されている、すべてのセルを選択できました。

同じ条件付き書式のセル

指定したセルと同じ、データの入力規則が設定されているセルを選択する、VBAコードは次のようになります。

Sub TEST15()
        
    '同じ、データの入力規則のセルを選択
    Range("B2").SpecialCells(xlCellTypeSameValidation).Select
    
End Sub

確認したいセル範囲を『Range("B2")』で指定しています。

この『Range("B2")』と、同じデータの入力規則が設定されている、セル範囲を選択します。

複数のデータの入力規則を設定した表を用意しました。

『B列』の『B4』だけ、違うデータの入力規則を設定しました。

一部だけ違うデータの入力規則を設定した表を用意

では、実行します。

xlCellTypeSameValidationで同じデータの入力規則のセルを選択

指定したセルと、同じデータの入力規則が設定されているセルを、選択できました。

可視セルを選択して不要行を削除

可視セルを選択して不要行を削除する、というのをやってみます。

ちょっと実務的な内容です。

表の中から不要なデータを削除したい、という場合に使えます。

可視セルのみを削除

可視セルのみを表の中から削除する、というのをやってみます。

使う表はこちらです。

使う表

使う表

この表から、A列が『"A"』である行を削除してみます。

VBAコードをこんな感じで作ってみました。

Sub TEST16()
    
    'A列で「A」以外である行を非表示
    With ActiveSheet
        For i = 2 To .Cells(Rows.Count, "A").End(xlUp).Row
            If .Cells(i, "A") <> "A" Then
                .Rows(i).Hidden = True '非表示
            End If
        Next
    End With
    
    '表全体の値のみを取得
    With ActiveSheet.Range("A1").CurrentRegion
        Set a = .Resize(.Rows.Count - 1).Offset(1, 0)
    End With
    
    '可視セルのみを削除
    a.SpecialCells(xlCellTypeVisible).Delete
    
    'すべての行を表示
    With ActiveSheet
        .Rows("1:100").Hidden = False
    End With
    
End Sub

手順としては、

  • ①A列が『A』以外である行を非表示にする
  • ②表全体の値のみを取得する
  • ③可視セルを削除する
  • ④行をすべて表示する

という感じです。

では、実行してみます。

A列の『"A"』の行を削除

SpecialCellsを使って表の中から、一部の表を削除した結果

A列が『"A"』である行だけを削除できました。

ただ、これについてはもっといいやり方があります。

オートフィルタを使う方法です。

オートフィルタで表示行のみを削除

オートフィルタで表示行のみを削除する、というのをやってみます。

使う表は先ほどと同じです。

使う表

使う表

この表から、A列が『"A"』である行を削除してみます。

VBAコードはこちらです。

Sub TEST17()
    
    'A列を、「A」でフィルタ
    With ActiveSheet
        .Range("A1").AutoFilter 1, "A"
    End With
    
    'フィルタした行を削除
    With ActiveSheet.Range("A1").CurrentRegion
        Application.DisplayAlerts = False '警告の表示を非表示
        .Resize(.Rows.Count - 1).Offset(1, 0).Delete
        Application.DisplayAlerts = True '警告の表示を表示
    End With
    
    'フィルタを解除
    With ActiveSheet
        .ShowAllData
    End With
    
End Sub

VBAコードの流れとしては、

  • ①オートフィルタでA列を『"A"』でフィルタ
  • ②表示している行を削除
  • ③フィルタを解除

という感じです。

実行してみます。

A列の『"A"』の行のみを削除

A列がAである行をオートフィルタを使って削除した結果

A列が『"A"』である行を削除できました。

SpecialCellsより、オートフィルタの方が簡単ですね。

じゃあSpecialCellsっていつ使うかですね。

SpecialCellsは、条件付き書式を見つけるとか、空白セルを見つける場合に使うといいです。

エラー探しみたいな感じですね。

おわりに

この記事では、条件に一致したセル範囲を取得する、SpecialCellsについて解説しました。

SpecialCellsを使えば、空白セルや、条件付き書式が設定された、セル範囲を取得することができます。

条件付き書式や、データの入力規則の設定漏れの確認に使えます。

参考になればと思います。最後までご覧くださいまして、ありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

アーカイブから探す