VBA 結合セルを扱う - 派遣事務員の迷走

こんにちは。
派犬事務員のコロ子です。

あるシステムからデータをダウンロードすると、こんな感じのエクセルの表になって落ちてくる。

f:id:SNegishi:20210209223635p:plain — 表1

見やすいようにセルが結合されている。
わざわざそういう風に作ってくれているのはありがたいけど、これを加工するとなるとちょっと扱いにくい。

結合されたセルの扱いについて考える

A1～A4が結合されている場合
f:id:SNegishi:20210209215905p:plain

結合セルA1：A4を選択するには、セルの範囲のどこを指定しても結合セルが選択される。
Cells(1, 1).Select
Cells(2, 1).Select
Cells(3, 1).Select
Cells(4, 1).Select
上記4つは全部同じ動きをする。

値を取るのはどうだろう。
Cells(1, 1).Value　←このときだけ値が取れる　
Cells(2, 1).Value　←空が取得
Cells(3, 1).Value　←空が取得
Cells(4, 1).Value　←空が取得
値は結合の一番上のセルでのみ取得可能。
確かにセルの結合を解除すると一番上のセルに値が入って他のセルは空欄になる。
なるほど、こういう仕様かぁ。

ちなみに、
f:id:SNegishi:20210211181934p:plain
①のように横に結合されている場合は一番左のセル（B2セル）
②のように縦横に結合されている場合は一番左上のセル（B4セル）
で値が取れる。

仕組みが分かったので、表1からサンプル名を取得してみる。

Sub SampleName()

Dim i As Long
For i = 4 To 17
    If Cells(i, 2).Value <> "" Then
        Debug.Print Cells(i, 2).Value
    End If
Next i

End Sub

次に、表1より「日付」「サンプル名」「ロット」「データ」を取得してみる。
Selectionで結合セルを選択して、アドレスから値を取得する方法でやってみる。

Sub データ()

    Dim ran As Range
    
    For Each ran In Range("D4:H17")
    
        Debug.Print "日付：" & セルの値_日付(ran, 2)
        Debug.Print "サンプル名：" & セルの値(ran, 2)
        Debug.Print "ロット番号：" & セルの値(ran, 3)
        Debug.Print "データ：" & ran.Value
    
    Next ran

End Sub

Function セルの値(ran As Range, c As Long) As String
'引数cは列番号
    
    Cells(ran.Row, c).Select

    Dim myAdress As Variant
    myAdress = Split(Selection.Address, "$")
    
    Dim r As Long
    '単独セルの場合
    If InStr(Selection.Address, ":") = 0 Then
        r = myAdress(2)
    '結合されている場合「：」を除く
    Else
        r = Left(myAdress(2), Len(myAdress(2)) - 1)
    End If
        
    セルの値 = Cells(r, c).Value
    
End Function

Function セルの値_日付(ran As Range, r As Long) As String
'引数rは行番号

    Cells(r, ran.Column).Select

    Dim myAdress As Variant
    myAdress = Split(Selection.Address, "$")
    
    Dim c As String
    c = myAdress(1)
        
    セルの値_日付 = Cells(r, c).Value & Cells(r + 1, ran.Column).Value

End Function

・セルのアドレスは$B$4:$B$10のような形になっているので、＄で区切って配列に入れる。
・縦に結合されている場合は、配列の2番目が行番号。「：」が入っていないなければ単独セル。「：」が入っている場合は：を除く。
・横に結合されている場合は配列の1番目が列番号。列番号は英文字で取得。

この場合Selectionでセルを選択しているのでマクロ実行中は絶対にエクセルを触らないように注意。