【VBA】データが途切れた回数をカウントする方法

質問

添付画像のような1行ごとにデータが入力されている表があります。
このデータには、データが入力されているセルと空白セル(黄色のセル)があります。
1行ごとに、最初にデータが入力されたセルから最後にデータが入力されたセルまでの間に、空白が発生した回数が何回あるのかをカウントしたいと思っております。
空白セルの数をカウントするのではなく、データが途切れた回数をカウントしたいのです。
VBAを使って、上記のような処理は可能でしょうか？
私は現在VBAを勉強中で知識が浅いため分かりませんでした。
もし可能であれば、その方法を教えていただけると大変助かります。
お手数お掛けいたしますが、よろしくお願い致します。

回答No.1

cj_mover · ベストアンサー

こんにちは。

> 空白セルの数をカウントするのではなく、データが途切れた回数をカウントしたいのです。
う～んと、そのご説明では、添付画像のサンプル上から、３、５、３、１、２という結果になるのでは？
と思いますが、添付画像のサンプルを解釈する限りでは、
「空白でない区間に挟まれた空白区間の数」みたいな感じでしょうか？
簡単そうでいて案外頭を悩ませますね。

添付画像のサンプルを再現して動作を確認したものを３例掲げます。
書き方は山ほどありますが、ポイントは３段階のフラグをどう扱うかという一点になります。
　各行毎、列方向に二重にループして
　　　空白でない区間に未だ当たってない場合の初期値として　　vbUseDefault
　　　空白区間から空白でない区間に入ったら　　vbFalse
　　　空白でない区間から空白区間に入ったら　　vbTrue
　　というように定義した変数を使って、
　　　vbTrue　が　vbFalse　に変わる（空白区間から空白でない区間に入る）段階
　　をカウントします。

' ' ///

Sub Re8516841()
　　Dim i As Long
　　Dim j As Long
　　Dim triIsBlank As VbTriState
　　Dim cnt As Long

　　For i = 3 To 7
　　　　triIsBlank = vbUseDefault
　　　　cnt = 0
　　　　For j = 2 To 17
　　　　　　If Cells(i, j) <> "" Then
　　　　　　　　If triIsBlank = vbTrue Then cnt = cnt + 1
　　　　　　　　If triIsBlank Then triIsBlank = vbFalse
　　　　　　ElseIf triIsBlank = vbFalse Then
　　　　　　　　triIsBlank = vbTrue
　　　　　　End If
　　　　Next j
　　　　Cells(i, 18) = cnt
　　Next i
End Sub

' ' ///

あえて馴染みやすい（？）ように、整数型で表すと、以下のようになります。

' ' ///

Sub Re8516841c()
　　Dim i As Long
　　Dim j As Long
　　Dim nMode As Integer
　　Dim cnt As Long

　　For i = 3 To 7
　　　　nMode = -2
　　　　cnt = 0
　　　　For j = 2 To 17
　　　　　　If Cells(i, j) <> "" Then
　　　　　　　　If nMode = -1 Then cnt = cnt + 1
　　　　　　　　If nMode Then nMode = 0
　　　　　　ElseIf nMode = 0 Then
　　　　　　　　nMode = -1
　　　　　　End If
　　　　Next j
　　　　Cells(i, 18) = cnt
　　Next i
End Sub

' ' ///

あえて２つの論理値を組み合わせて書くと、以下のようになります。
個々の記述については誰でも解る基本的な記述になっていますが、
全体としては却って、条件分岐の仕方が解り難いと感じる方も少なくないでしょう。

' ' ///

Sub Re8516841a()
　　Dim i As Long
　　Dim j As Long
　　Dim cnt As Long
　　Dim flgStart As Boolean
　　Dim flgOnBlank As Boolean

　　For i = 3 To 7
　　　　flgStart = False
　　　　flgOnBlank = False
　　　　cnt = 0
　　　　For j = 2 To 17
　　　　　　If Cells(i, j) <> "" Then
　　　　　　　　If flgOnBlank = True Then
　　　　　　　　cnt = cnt + 1
　　　　　　　　flgOnBlank = False
　　　　　　　　End If
　　　　　　　　If flgStart = False Then flgStart = True
　　　　　　ElseIf flgStart = True And flgOnBlank = False Then
　　　　　　　　flgOnBlank = True
　　　　　　End If
　　　　Next j
　　　　Cells(i, 18) = cnt
　　Next i
End Sub

' ' ///

大事なのは、使う方、ご本人が、解り易いと感じるものを使う、
ということになるかと思います。

お礼

cj_mover様

早々にご回答くださり誠にありがとうございます。
私の説明が足りなかったにも関わらず、意図を理解してくださり感謝しております。
ご指摘いただいたとおり「空白でない区間に挟まれた空白区間の数」を求めたかったのです。

「vbUseDefault」を使って空白区間から空白でない区間に入る段階をカウントするという方法があったのですね。
また、3パターンの記述例と大変分かりやすい解説もいただき、とても勉強になりました。

ご教示いただいたVBAで動作確認しましたら、希望通りの結果を得ることができました！
本当にありがとうございました(#^.^#)

回答No.3

mt2008

ANo.2です。
先ほどのユーザ関数では全て空の時に-1になってしまいますので、コードの最後の所を以下の様に修正してください。これで全て空の時は0になります。

fCount = UBound(sCount)
↓
fCount = WorksheetFunction.Max(0, UBound(sCount))

お礼

mt2008様

早々にご回答くださり誠にありがとうございます。

関数「Trim/Split/UBound」を組み合わせて、空白エリアをカウントするという方法はとても勉強になりました。

ご教示いただいたVBAで動作確認しましたら、希望通りの結果を得ることができました！
本当にありがとうございました(#^.^#)

回答No.2

mt2008

ユーザ関数を作ってみました。
下のコードを標準モジュールに入れた後、Ｒ3セルに
=fCount(B3:Q3)
の様に式を入れて下さい。

Function fCount(rRange) As Long
　　Dim rOne As Range
　　sSTring = ""
　　For Each rOne In rRange
　　　　sOne = rOne.Text
　　　　If sOne = "" Then sOne = " "
　　　　sSTring = sSTring & sOne
　　Next rOne
　　sSTring = WorksheetFunction.Trim(sSTring)
　　sCount = Split(sSTring, " ")
　　fCount = UBound(sCount)
End Function

セル範囲の文字を全部（空白はスペースに置換して）くっ付け、ワークシート関数のTrimで余計なスペースを除去した後でSplit関数で文字を分割し、その要素数を返しています。