EXCEL VBA 空白のセル・数式のセルを参照（SpecialCells）

2020年3月10日

EXCEL VBA 空白のセル・数式のセルを参照（SpecialCells）

●はじめに

EXCELの表などに、空白のセルや数式のセルが入力されているセルを「SpecialCells」メゾットを利用して指定したセルの範囲から条件を指定して、該当するセルを参照する「SpecialCells」の利用方法を説明いたします。

●書式の説明

Rangeオブジェクト.SpecialCells(Type：定数,Value:定数)

Type(定数)	説明
xlCellTypeBlanks	空白のセル
xlCellTypeFormulas	数式が含まれるセル
xlCellTypeAllFormatConditions	表示形式が設定されているセル
xlCellTypeAllValidation	条件の設定が含まれるセル
xlCellTypeComments	コメントが含まれるセル
xlCellTypeConstants	定数が含まれるセル
xlCellTypeLastCell	使われたセル範囲内の最後のセル
xlCellTypeFormatConditions	同じ表示形式が設定されているセル
xlCellTypeSameVlidation	同じ条件の設定が含まれるセル
xlCellTypeVisible	すべての可視セル

Value(定数)	説明
xlErrors	エラー値
xlLogical	論理値
xlNumbers	数値
xlTextValues	文字

空白のセルを数える・空白セルを参照する　「SpecialCells.xlCellTypeBlanks」

●プログラム説明　サンプルプログラム①　　　　（画像クリックで拡大）

下記のサンプルプログラムは、社内で従業員満足度を行い、アンケート結果の記入欄に空白（未記入）があれば、その数をMsgboxで表示させます。

Sub SpecialCells01()

    Dim A As Integer
    
    On Error Resume Next  'エラー発生しても続行

    A = Range("B2:B11").SpecialCells(xlCellTypeBlanks).Count  'B2～B11に未記入（空白）セルをカウントします。

    If A < 10 Then
            MsgBox "アンケートの記入漏れは、" & A & "件です"
        Else
            MsgBox "全てのアンケートが記入されています。"
        
    End If
        
End Sub

Sub SpecialCells01()

Dim A As Integer

On Error Resume Next 'エラー発生しても続行

A = Range("B2:B11").SpecialCells(xlCellTypeBlanks).Count 'B2～B11に未記入（空白）セルをカウントします。

If A < 10 Then

MsgBox "アンケートの記入漏れは、" & A & "件です"

Else

MsgBox "全てのアンケートが記入されています。"

End If

End Sub

●実行後　　：プログラム実行後、アンケート記入内容に２件未記入箇所があるので、メッセージボックスに「アンケートの記入漏れは、2件です」と表示されました。
（画像クリックして拡大）

表の数値・文字・数式部分を参照する　「SpecialCells.xlCellTypeConstants」・「SpecialCells.xlCellTypeFormulas」

●プログラム説明　サンプルプログラム②　　　　（画像クリックで拡大）

下記のサンプルプログラムは、下表の「地域別来客数実績表」にある。数値データの件数及び文字列のデータ件数・数式のデータ件数をカウントしてメッセージボックスに件数を表示させます。

Sub SpecialCells02()

    Dim A, B, C As Integer
    
    On Error Resume Next  'エラー発生しても続行
    
    A = Range("A3:J14").SpecialCells(xlCellTypeConstants, xlNumbers).Count '定数(数値)のデータ件数 （青セル）
    
    B = Range("A3:J14").SpecialCells(xlCellTypeConstants, xlTextValues).Count  '定数（文字）のデータ件数　（赤セル）
    
    C = Range("A3:J14").SpecialCells(xlCellTypeFormulas).Count '数式のデータ件数　（黄色セル）

    MsgBox "定数(数値)のデータ件数は、" & A & "件" & vbCrLf & "定数(文字)のデータ件数は、" & B & "件" _
           & vbCrLf & "数式のデータ件数は、" & C & "件"
          
End Sub

Sub SpecialCells02()

Dim A, B, C As Integer

On Error Resume Next 'エラー発生しても続行

A = Range("A3:J14").SpecialCells(xlCellTypeConstants, xlNumbers).Count '定数(数値)のデータ件数（青セル）

B = Range("A3:J14").SpecialCells(xlCellTypeConstants, xlTextValues).Count '定数（文字）のデータ件数　（赤セル）

C = Range("A3:J14").SpecialCells(xlCellTypeFormulas).Count '数式のデータ件数　（黄色セル）

MsgBox "定数(数値)のデータ件数は、" & A & "件" & vbCrLf & "定数(文字)のデータ件数は、" & B & "件" _

& vbCrLf & "数式のデータ件数は、" & C & "件"

End Sub

●実行後　：プログラム実行後、表の数値のデータ件数・文字のデータ件数・数式のデータ件数が表示されました。（画像クリックして拡大）

数式を残して数値を削除・数式を参照する　「SpecialCells.xlCellTypeConstants, xlNumbers」

●プログラム説明　サンプルプログラム③　　（画像クリックで拡大）

下記のサンプルプログラムは、下表「地域別来客数実績表」に入力されている数値（入力値）のみ削除するプログラムです。計算式が入力されている部分については、そのままなので表を何度も利用する際には、便利な使い方になります。

Sub SpecialCells03()

    Dim Ans As Integer
      
    Ans = MsgBox("入力されている数値を削除しますか？", vbYesNo + vbQuestion, "確認")
    
        If Ans = vbYes Then  'Yesの場合は、実行します。
                On Error Resume Next  'エラー発生しても続行
                Range("A3:J14").SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents  '数値の入力されているセルを削除します。
    
                MsgBox "数値が入力されているセルをクリアしました。"
        
        Else
            MsgBox "処理を中断します"
        
        End If
    
End Sub