VBA 空白セル検出

Question

お世話になっております。

C3～G2000の範囲で空白セルを検出し、
空白セルが合った場合には、空白セルに"-"を入れたいのでが、
効率よくできる方法はないでしょうか？
本当は、セル範囲は可変にしたいです。毎回同じ範囲にデーターが収まるとは限らないので。

自分の場合は、各列をループで一つ一つのセルを空白か否かを
判定するコードにしたのですが、これだと、列分、同じコードを
書くことになり、非効率ですし。

でも、CurrentRegionは一体どこまでの範囲か不明確ですし。

↓こんな非効率なコードになってしまいます。
まだまだ、勉強不足を痛感しております。

Sub test()
    Dim C_MaxRow As Long 'C列の最終行
    Dim i As Long
    
    D_MaxRow = Cells(Rows.Count, 4).End(xlUp).Row - 2
'    MsgBox D_MaxRow

    For i = 3 To C_MaxRow
        If Cells(i, 2).Value <> "" Then
         'データーがある場合は、何もしない。
        Else
            Cells(i, 2).Value = "-"
        End If
    Next
    
     For i = 3 To C_MaxRow
        If Cells(i, 3).Value <> "" Then
        
        Else
            Cells(i, 3).Value = "-"
        End If
    Next
        
    For i = 3 To C_MaxRow
        If Cells(i, 4).Value <> "" Then
        
        Else
            Cells(i, 4).Value = "-"
        End If
    Next
    
     For i = 3 To C_MaxRow
        If Cells(i, 5).Value <> "" Then
        
        Else
            Cells(i, 5).Value = "-"
        End If
    Next
End Sub

どなたかご教授願います。

Answer 1

ynakanoさんの置換する方法は、今回やりたいことにマッチしており、かつシンプルでいい案だと思います。

私からは技術的なアドバイスとして、指定範囲のループを簡潔に記述する方法、という観点で提示いただいたソースコードの改良案を提案させていただきます。

動的な範囲選択

まず、動的な範囲選択についてですが、最終行を調べる.End(xlUp).Rowはもう実装されているようですので、行方向への動的な範囲選択はできています。
もし列方向に対しても動的に範囲が変更されるようでしたら、例えばCells(2, Columns.Count).End(xlToLeft).Columnとすることで2行目の最終列が取得できます。

ループ処理のアドバイス①

提示いただいたサンプルコードで「非効率」と思われている箇所は、ほぼ同じ内容のループ処理を何度も繰り返している部分ですよね。
確かに見た目にも美しくありませんし、ちょっと変更したいとき（例えば""だけでなく" "も変換したいとき）にたくさんの箇所に同じ修正が必要になってしまいます。

各ループ処理の中では、列番号の部分だけが2,3,4,5と変更されているだけですので、これもループ変数にしてしまえば記述をまとめることができます。

サンプルソース①

Sub test()
    Dim C_MaxRow As Long 'C列の最終行
    Dim iCol As Long
    Dim iRow As Long
    
    'C列最終行を取得
    C_MaxRow = Cells(Rows.Count, 3).End(xlUp).Row
    
    For iCol = 2 To 5  'B～E列をループ処理
        For iRow = 3 To C_MaxRow    '3行目～C列最終データ行をループ処理
            If Cells(iRow, viColumn).Value <> "" Then
             'データーがある場合は、何もしない。
            Else
                Cells(iRow, viColumn).Value = "-"
            End If
        Next
    Next
    
End Sub

ループ処理のアドバイス②

アドバイス①でも問題はありませんが、自分がVBAで選択範囲のループ処理を行うのなら以下のような記述にします。

サンプルソース②

Sub test()
    Dim C_MaxRow As Long 'C列の最終行
    
    'C列最終行を取得
    C_MaxRow = Cells(Rows.Count, "C").End(xlUp).Row
    
    Dim rngArea As Range    '対象範囲
    Set rngArea = Range(Cells(3, "B"), Cells(C_MaxRow, "E"))    'ここではB～E列の3行目～C列最終データ行の範囲としています。
    
    Dim rng As Variant  '範囲内のセルを格納する変数
    For Each rng In rngArea
        If rng.Value <> "" Then
         'データーがある場合は、何もしない。
        Else
            rng.Value = "-"
        End If
    Next

End Sub

For Each ～ In 対象範囲のようなループ処理を記述することで、その範囲内のすべてのセルを１つずつ取り出すことができます。
目的からいってもこのほうが分かりやすい記述になると思います。

どちらを使っても（またはynakanoさんの案のような別の方法を使っても）、今回は同じ結果が得られると思います。
好みに応じて、また今後のメンテナンス性も考えて、一番使いやすいものを選択されればよいでしょう。

参考になれば幸いです。

Answer 2

一つ一つ空白を探すのではなく「置換」を使うのがよいと思います。
範囲を選択して

Selection.Replace What:="", Replacement:="-"

でOKかと。

また、セル範囲可変は以下で入力済セルの最終行を取得できます。
Cells(Rows.Count, "A").End(xlUp).Row
これを変数で受ければOKです。
※上記はA列の末尾を探す記述になっています。

追記

左側のかたまりの部分についてコードを書いてみました。
ちょっと泥臭い書き方で、かつ一部決め打ちの部分があります。

Sub test()
    Dim curMax As Integer
    Dim totalMax As Integer
    Dim myArray1() As Variant

    myArray1 = Array("C", "D", "E")

    For Each TG In myArray1
        curMax = Cells(Rows.Count, TG).End(xlUp).Row
    
        If (curMax > totalMax) Then totalMax = curMax
    Next
    
    Range("C3:E" & totalMax).Replace What:="", Replacement:="-"
End Sub

Comments

[退会済みユーザー]

イメージ図を追加させていただきました。

[macinspire]

下の回答者です。
Cells(1,"A")
のように、列は文字でも指定ができるのですね。
目から鱗でした。

Answer 3

vbaを始めたばかりの方にはとっつきにくいかもしれませんが、
範囲を定義して、範囲のセルを１つずつ見て処理、という流れもできます。
範囲が可変ならば、範囲を取得するロジックを考えるとか、
ユーザーにテーブル名をつけてもらい、range(テーブル名)で取得するとか、
工夫や運用で対策をします。

Sub test2()
Dim MyRng As Range
Dim MyCell As Range
Set MyRng = Range(左上端のセル, 右下端のセル) '処理したい範囲を定義
For Each MyCell In MyRng.Cells '範囲が含む該当すべてについて
If MyCell.Value = "" Then '値がなければ
MyCell.Value = "-"
End If
Next
End Sub