エクセルバージョン
マイクロソフト365
今回はVBAで空白個数を取得する方法を解説します。
それではいってみましょう!
Menu
今回紹介する3つの方法
・ワークシート関数のCOUNTBLANK関数を使用する。
・ForNext(1行自動取得の場合)を使用する。
・UsedRange(範囲)を使用する。
今回はこの3つの方法について解説します。
ワークシート関数COUNTBLANK
結果が出るまでのスピードが超高速です。
1000万個以上の空白セルの個数でも、一瞬で空白個数を取得します(*^^)v
定数ConstのSTARTPOSは範囲の左上のセルを指定
定数ConstのENDPOSは範囲の右下のセルを指定
コピペで即試せます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | Option Explicit '定数 Const STARTPOS As String = "A1" '範囲の左上セルを指定 Const ENDPOS As String = "K1048576" '範囲の右下セルを指定 Sub ワークシートCOUNTBLANK関数1() '変数① Dim wsCntBlank As Long 'ワークシート関数のCountBlankを使用② wsCntBlank = WorksheetFunction.CountBlank(Range(STARTPOS & ":" & ENDPOS)) 'メッセージボックスに空白個数を表示③ MsgBox wsCntBlank End Sub |
【解説】
➀変数を宣言します。
➁ワークシート関数のCountBlank関数を使用して空白セルの個数を求めます。
③結果をメッセージボックスに表示で完成です\(^^)/
【結果】
1000万セル以上でも、一瞬で空白個数が表示されます!
すごっ!(((( ;゚Д゚)))ガクガクブルブル
※エクセルのバージョンよってページの最終行が違うので、ご自身のバージョンに合った最終セル(定数ConstのENDPOSの右下のセル)の値をご指定ください。
今回の結果はバージョンがマイクロソフト365の場合の結果です。
ForNext(1行の範囲を自動選択する場合)
スピード的には先程のCountBlank関数には劣るものの、1行で下に増えていくデータの範囲を自動で範囲選択して個数を取得する場合に便利に機能します。
ForNextで繰り返し空白セルがあるかIF文で判定して個数を取得します。
データ型のIntegerの3万個くらいのセルを調べたい場合、速さも十分使用できるレベルの速さです。
定数ConstのCOLは列を指定します。
定数ConstのSTARTROWは行頭番号を指定します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | Option Explicit '定数 Const COL As String = "A" '列を指定 Const STARTROW As Integer = 1 '行頭番号を指定 Sub 一行のみの空白数() '変数① Dim intRow, blankCnt, lastRow As Integer 'データの最終行番号➁ lastRow = Range(COL & Rows.Count).End(xlUp).Row 'ForNextで繰り返し処理③ For intRow = STARTROW To lastRow 'IF文で空白だったら処理➃ If Cells(intRow, COL) = "" Then blankCnt = blankCnt + 1 '空白をプラス1していく➄ End If Next 'メッセージボックスに空白セル合計を表示➅ MsgBox blankCnt End Sub |
【解説】
➀変数を宣言します。
➁データの最終行番号を取得します。
③FoNextで繰り返し処理をおこないます。
➃IF文で空白だったら処理をします。
➄変数blankCntに、1ずつ値を加算させて合計を算出します。
➅結果をメッセージボックスに表示して完成です(^o^)o
UsedRange(範囲を自動選択する場合)
UsedEangeプロパティを使用してデータ範囲の空白個数を取得する例。
こちらはセル範囲の形式がUsedEangeに合う場合に有効に動作しますが、範囲の形式がそうでない場合は全く機能しないので、その点だけ注意してください。
ForEachでデータ範囲の空白セルをIF文で空白判定をして、変数cntに1ずつ追加してくと空白セルの合計が算出できます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | Sub UsedRangeで空白個数取得() '変数① Dim cnt As Integer Dim rng, uRng As Range 'オブジェクト変数➁ Set uRng = ActiveSheet.UsedRange '空白数を取得③ For Each rng In uRng If rng = "" Then cnt = cnt + 1 End If Next '空白数をメッセージボックスに表示④ MsgBox cnt '範囲確認➄ uRng.Select 'オブジェクト開放 Set uRng = Nothing End Sub |
【解説】
➀変数を宣言します。
➁オブジェクト変数にUsedRangeの範囲をセットします。
③ForEachで繰り返し処理を行い、IF文で空白セルだったら処理を実施して、変数cntに1ずつ値を加算させて合計を求めます。
➃メッセージボックスに空白セルの合計を表示させます。
➄最後に一応データ範囲をSelectで目視確認して完成です(*^^)v
【結果】
最後にデータ範囲が合ってるか確認します(★‿★)
まとめ
ワークシート関数のCOUNTBLANK関数を使用すれば超高速で空白個数を取得できるので、1000万セルとか(それ以上も可)、もしある場合はご使用してみてください(^o^)o
以上です。
複数の空白セルを非表示
複数の空白セルを削除
【関数】複数の空白セルを削除(SMALLとINDIRECT)
複数の空白セルに一括入力
複数の空白行
空白セルの個数
セルの中の空白
【VBA】セル内の文字列の空白を削除(Trim,RTrim.LTrim)