－ちょっと使えるネタを紹介－
Excel VBAで、可視セルや空白セルなどの、条件に一致したセル範囲を取得する方法について、ご紹介します。使うVBA関数は、SpecialCellsです。SpecialCellsを使えば、条件付き書式やデータ入力規則などの、設定漏れを確認することができます。
この記事では、条件に一致したセル範囲を取得する、SpecialCellsについて解説します。
SpecialCellsを使えば、可視セルや、空白セル、条件付き書式が設定されたセル範囲を、取得することができます。
条件付き書式や、データの入力規則の設定漏れの確認に使えます。
では、具体的なVBAコードを使って、解説していきます。
この記事で紹介すること
目次から見たい項目へ移動すると便利ですよ。
目次
条件に一致したセル範囲を取得する、SpecialCellsについて、まとめました。
こんな感じです。
'コメント Cells.SpecialCells(xlCellTypeComments).Select '定数 Cells.SpecialCells(xlCellTypeConstants).Select 'すべて Cells.SpecialCells(xlCellTypeConstants, 1).Select '数値 Cells.SpecialCells(xlCellTypeConstants, 2).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コードを実行してみます。
条件に一致するセル範囲を取得するSpecialCellsは、次のセル範囲を取得できます。
という感じです。
では、一つずつ動きをみてみましょう。
コメントが入力されているセルを選択するには、『xlCellTypeComments』を入力します。
VBAコードは、次のようになります。
Sub TEST1()
'コメントのセルを選択
Cells.SpecialCells(xlCellTypeComments).Select
End Sub
セル範囲は設定せず、『Cells』だけでできます。
コメントを入力したセルを用意しました。
では、実行します。
コメントが入力されているセルを、選択できました。
次は、定数が入力されているセルを選択する方法です。『xlCellTypeConstants』を入力します。
定数のセル選択では、次の値を区別して選択できます。
まずは、定数のすべてを選択してみます。
定数の全てを選択する、VBAコードは、次のようになります。
Sub TEST2()
'定数のセルを選択
Cells.SpecialCells(xlCellTypeConstants).Select
End Sub
セル範囲は設定せず、『Cells』だけでできます。
すべて定数で入力した表を用意しました。
では、実行します。
定数が入力されているセルを、選択できました。
すべて定数で入力しているので、表の全てが選択されています。
次は、定数の内、数値を選択してみます。2番目の引数に『1』を入力します。
定数の数値を選択する、VBAコードは、次のようになります。
Sub TEST3()
'定数で数値のセルを選択
Cells.SpecialCells(xlCellTypeConstants, 1).Select
End Sub
セル範囲は設定せず、『Cells』だけでできます。
先ほどと同じ表を使います。
では、実行します。
定数で数値が入力されているセルを、選択できました。
こんな感じで、数値だけの選択もできます。
次は、定数の内、文字を選択してみます。2番目の引数に『2』を入力します。
定数の文字を選択する、VBAコードは、次のようになります。
Sub TEST4()
'定数で文字のセルを選択
Cells.SpecialCells(xlCellTypeConstants, 2).Select
End Sub
セル範囲は設定せず、『Cells』だけでできます。
先ほどと同じ表を使います。
では、実行します。
定数で文字が入力されているセルを、選択できました。
文字だけの選択もできます。
次は、数式が入力されているセルを選択する方法です。『xlCellTypeFormulas』を入力します。
数式のセル選択では、次の値を区別して選択できます。
まずは、数式のすべてを選択してみます。
数式の全てを選択する、VBAコードは、次のようになります。
Sub TEST5()
'数式のセルを選択
Cells.SpecialCells(xlCellTypeFormulas).Select
End Sub
セル範囲は設定せず、『Cells』だけでできます。
『E列とF列』に数式を入力した表を用意しました。
では、実行します。
数式が入力されているセルを、選択できました。
E列とF列に数式を入力しているので、E列とF列が選択されています。
次は、数式の内、数値を選択してみます。2番目の引数に『1』を入力します。
数式の数値を選択する、VBAコードは次のようになります。
Sub TEST6()
'数式で数値のセルを選択
Cells.SpecialCells(xlCellTypeFormulas, 1).Select
End Sub
セル範囲は設定せず、『Cells』だけでできます。
先ほどと同じ表を使います。
では、実行します。
数式で数値が入力されているセルを、選択できました。
F列にも数式は入力されていますけども、結果が文字なので、選択されていません。
こんな感じで、数値だけの選択もできます。
次は、数式の内、文字を選択してみます。2番目の引数に『2』を入力します。
定数の文字を選択する、VBAコードは、次のようになります。
Sub TEST7()
'数式で文字のセルを選択
Cells.SpecialCells(xlCellTypeFormulas, 2).Select
End Sub
セル範囲は設定せず、『Cells』だけでできます。
先ほどと同じ表を使います。
E列とF列に、数式が入力されています。
では、実行します。
数式で文字が入力されているセルを、選択できました。
E列も、数式が入力されていますけども、結果が数値なので、選択はされません。
こんな感じで、数式で文字の選択もできます。
次は、数式の内、エラーとなっているセルを選択してみます。2番目の引数に『16』を入力します。
数式のエラーを選択する、VBAコードは次のようになります。
Sub TEST8()
'数式でエラーのセルを選択
Cells.SpecialCells(xlCellTypeFormulas, 16).Select
End Sub
セル範囲は設定せず、『Cells』だけでできます。
先ほどと同じ表を使います。
では、実行します。
数式でエラーとなっているセルを、選択できました。
数式のエラー検索に使えます。
次は、空白セルを選択してみます。
空白セルの選択には、『xlCellTypeBlanks』を使います。
空白セルを選択する、VBAコードは次のようになります。
Sub TEST9()
'空白のセルを選択
Cells.SpecialCells(xlCellTypeBlanks).Select
End Sub
セル範囲は設定せず、『Cells』だけでできます。
表の一部を空白にした表を用意しました。
では、実行します。
空白セルを、選択できました。
次は、最後のセルを選択してみます。
最後のセルの選択には、『xlCellTypeLastCell』を使います。
最後のセルを選択する、VBAコードは次のようになります。
Sub TEST10()
'最後のセルを選択
Cells.SpecialCells(xlCellTypeLastCell).Select
End Sub
セル範囲は設定せず、『Cells』だけでできます。
次のような表を用意しました。
では、実行します。
最後のセルを、選択できました。
最後のセルを選択する際に、注意があります。
行の高さ変更や、書式の変更したセルでも、最後のセルとして認識してしまいます。
11行目で、行の高さを変更してみました。
同じVBAコードを実行してみます。
行の高さを変更したところのセルが、選択されました。
『xlCellTypeLastCell』を使う際の、注意でした。
次は、可視セルを選択してみます。
可視セルの選択には、『xlCellTypeVisible』を使います。
可視セルを選択する、VBAコードは次のようになります。
Sub TEST11()
'可視セルを選択
Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Select
End Sub
可視セルを選択する際は、範囲を指定する必要があります。
なので、『Range("A1").CurrentRegion』で、表のセル範囲を指定しています。
表の一部を非表示にした表を用意しました。
では、実行します。
可視セルを、選択できました。
選択範囲が、とびとびになっていますね。
表示されているセル範囲だけが、選択されていることがわかります。
次は、条件付き書式が設定されているセルを選択してみます。
条件付き書式のセル選択には、2種類あります。
それぞれ、
を使います。
すべての条件付き書式のセルを選択する、VBAコードは次のようになります。
Sub TEST12()
'すべての条件付き書式のセルを選択
Cells.SpecialCells(xlCellTypeAllFormatConditions).Select
End Sub
セル範囲は設定せず、『Cells』だけでできます。
条件付き書式を設定した表を用意しました。
では、実行します。
すべての条件付き書式のセルを、選択できました。
すべてのセルに条件付き書式が設定されているので、正しく選択できていることがわかります。
次は、同じ条件付き書式が設定されているセルを選択してみます。
VBAコードは次のようになります。
Sub TEST13()
'同じ条件付き書式のセルを選択
Range("A1").SpecialCells(xlCellTypeSameFormatConditions).Select
End Sub
確認したいセルを『Range("A1")』で指定しています。
この『Range("A1")』に設定されている条件付き書式と、同じ条件付き書式が設定されているセル範囲を選択します。
複数の条件付きが設定された表を用意しました。
色別に、同じ条件付き書式を設定しています。
では、実行します。
同じ条件付き書式のセルを、選択できました。
色別に同じ条件付き書式が設定されていて、同じ背景色のセルが選択されているので、正しく選択されています。
次は、データの入力規則が設定されているセルを選択してみます。
データの入力規則のセル選択には、2種類あります。
それぞれ、
を使います。
データの入力規則が設定されている、すべてのセルを選択する、VBAコードは次のようになります。
Sub TEST14()
'すべての、データの入力規則のセルを選択
Cells.SpecialCells(xlCellTypeAllValidation).Select
End Sub
セル範囲は設定せず、『Cells』だけでできます。
データの入力規則を設定した表を用意しました。
『B列』にデータの入力規則が設定されています。
では、実行します。
データの入力規則が設定されている、すべてのセルを選択できました。
指定したセルと同じ、データの入力規則が設定されているセルを選択する、VBAコードは次のようになります。
Sub TEST15()
'同じ、データの入力規則のセルを選択
Range("B2").SpecialCells(xlCellTypeSameValidation).Select
End Sub
確認したいセル範囲を『Range("B2")』で指定しています。
この『Range("B2")』と、同じデータの入力規則が設定されている、セル範囲を選択します。
複数のデータの入力規則を設定した表を用意しました。
『B列』の『B4』だけ、違うデータの入力規則を設定しました。
では、実行します。
指定したセルと、同じデータの入力規則が設定されているセルを、選択できました。
可視セルを選択して不要行を削除する、というのをやってみます。
ちょっと実務的な内容です。
表の中から不要なデータを削除したい、という場合に使えます。
可視セルのみを表の中から削除する、というのをやってみます。
使う表はこちらです。
この表から、A列が『"A"』である行を削除してみます。
VBAコードをこんな感じで作ってみました。
Sub TEST16()
'A列で「A」以外である行を非表示
For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row
If Cells(i, "A") <> "A" Then
Rows(i).Hidden = True '非表示
End If
Next
'表全体の値のみを取得
With Range("A1").CurrentRegion.Offset(1, 0)
.Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Delete
End With
'すべての行を表示
Rows("1:100").Hidden = False
End Sub
手順としては、
という感じです。
では、実行してみます。
A列が『"A"』である行だけを削除できました。
ただ、これについてはもっといいやり方があります。
オートフィルタを使う方法です。
オートフィルタで表示行のみを削除する、というのをやってみます。
使う表は先ほどと同じです。
この表から、A列が『"A"』である行を削除してみます。
VBAコードはこちらです。
Sub TEST17()
'A列を、「A」でフィルタ
Range("A1").AutoFilter 1, "A"
'フィルタした行を削除
With Range("A1").CurrentRegion.Offset(1, 0)
.Resize(.Rows.Count - 1).EntireRow.Delete
End With
'フィルタを解除
Range("A1").AutoFilter
End Sub
VBAコードの流れとしては、
という感じです。
実行してみます。
A列が『"A"』である行を削除できました。
SpecialCellsより、オートフィルタの方が簡単ですね。
じゃあSpecialCellsっていつ使うかですね。
SpecialCellsは、条件付き書式を見つけるとか、空白セルを見つける場合に使うといいです。
エラー探しみたいな感じですね。
この記事では、条件に一致したセル範囲を取得する、SpecialCellsについて解説しました。
SpecialCellsを使えば、空白セルや、条件付き書式が設定された、セル範囲を取得することができます。
条件付き書式や、データの入力規則の設定漏れの確認に使えます。
参考になればと思います。最後までご覧くださいまして、ありがとうございました。
関連する記事から探す
カテゴリから見たい項目を探すと便利ですよ。
↓キーワードを入力する