文字列の抽出と計算　３．VBA：半角スペースの利用 ④

はじめに
今回のテーマ
文字列の分割と最終行の取得
おわりに
- メリット　：
- デメリット：

はじめに

前回は文字列を半角スペースで分割し、繰り返し処理を入れ子にして全て配列に格納しました。

infoment.hatenablog.com

今回も、前回の内容を改良してみましょう。

今回のテーマ

前回と同じテーマです。
一つのセルに、３桁の数字が３つあります。これらは、半角スペースで区切られています。この数字について、真ん中のグループの和を求めてみましょう。
f:id:Infoment:20180619063141p:plain

文字列の分割と最終行の取得

不幸にして、データの中に空白行がある場合、今までの方法では全て、最終行番号を正しく取得することができません。

↓前回のコード↓

Sub myCalc()

    Dim i As Long
    Dim j As Long
    Dim iMax As Long
    Dim splitResult() As Variant

    iMax = Cells(2, 1).End(xlDown).Row - 1
    Cells(iMax + 1, 2) = 0

' 二次配列を再宣言
    ReDim splitResult(2 To iMax, 1 To 3)

    For i = 2 To iMax
        For j = 1 To 3
            splitResult(i, j) = Split(Cells(i, 1))(j - 1)
        Next j
        Cells(iMax + 1, 2) = Cells(iMax + 1, 2) + splitResult(i, 2)
    Next
    
End Sub

なぜなら「End(xlDown)」が「連続するデータの一番下」を指しているからです。

例↓）４行目に空白がある場合。
f:id:Infoment:20180626062121p:plain

従って上記のマクロを実行すると、このように、正しくない結果になります。
f:id:Infoment:20180626062514p:plain

そこで、次のようにコードを修正します。

Sub myCalc()

    Dim i As Long
    Dim j As Long
    Dim iMax As Long
    Dim splitResult() As Variant

' 最終行番号の取得
    iMax = Cells(Rows.Count, 1).End(xlUp).Row - 1
    
    Cells(iMax + 1, 2) = 0

' 二次配列を再宣言
    ReDim splitResult(2 To iMax, 1 To 3)

    For i = 2 To iMax
        For j = 1 To 3
            splitResult(i, j) = Split(Cells(i, 1))(j - 1)
        Next j
        Cells(iMax + 1, 2) = Cells(iMax + 1, 2) + splitResult(i, 2)
    Next
    
End Sub

修正したのは、この個所です。

' 最終行番号の取得
    iMax = Cells(Rows.Count, 1).End(xlUp).Row - 1

Rows.Count はExcel シートの全行数です。Excel2013 であれば、1048576行あります。従って、修正箇所はこのように置き換えることができます。

' 最終行番号の取得
    iMax = Cells(Rows.Count, 1).End(xlUp).Row - 1
'               ↓
    iMax = Range("A1048576").End(xlUp).Row - 1

End(xlup) は、End(xldown) の逆です。空白行を全て飛び越し、何かが入力されている最初のセルまで一気にジャンプします。今回の場合でいえば、それは A6 です。従って修正箇所は、さらにこのように置き換えることができます。

' 最終行番号の取得
    iMax = Range("A1048576").End(xlUp).Row - 1
'               ↓
    iMax = Range("A6").Row - 1
'               ↓
    iMax = 6 - 1
'               ↓
    iMax = 5

これなら下から探しに行くため、途中に空白行があっても、正しく最終行番号を取得することができます。

ただしこのままでは、マクロはエラーになります。
f:id:Infoment:20180626064847p:plain

「デバッグ」ボタンを押すと、エラー箇所がハイライト表示されています。
f:id:Infoment:20180626064928p:plain

何もない、空っぽのセルの中身を半角スペースで区切らせようとしたため、このようなエラーが発生しました。
対処方法は幾つかありますが、一番簡単なのは、エラーを無視するという方法です。

Sub myCalc()

    Dim i As Long
    Dim j As Long
    Dim iMax As Long
    Dim splitResult() As Variant

' 最終行番号の取得
    iMax = Cells(Rows.Count, 1).End(xlUp).Row - 1
    
    Cells(iMax + 1, 2) = 0

' 二次配列を再宣言
    ReDim splitResult(2 To iMax, 1 To 3)

    For i = 2 To iMax
        For j = 1 To 3
        
        ' エラーがあっても無視して処理を続ける
            On Error Resume Next
            splitResult(i, j) = Split(Cells(i, 1))(j - 1)
        Next j
        Cells(iMax + 1, 2) = Cells(iMax + 1, 2) + splitResult(i, 2)
    Next
    
End Sub