はじめに
エクセルVBAを使っているとよく最終行番号、最終列番号を取得する処理が出てくると思います。
うまく最終行番号を取得できない場合があり、原因が結合セルでした。
結合セルに対応した最終行番号・最終列番号を取得するメソッドをご紹介します。
（結合セルがない場合もそのまま使用できます）
最終行番号を取得するメソッド
'引数①のシートの引数②の列の最終入力行番号を返す
'
'@param Worksheet sh 対象のワークシート
'@param Long targetCol 対象行
'@return Long 最終行から上方向に見て最初に入力のあるセルの行番号
Function GetLastRowNum(ByVal sh As Worksheet, ByVal targetCol As Long) As Long
Dim lastRow As Long
lastRow = sh.Cells(Rows.Count, targetCol).End(xlUp).Row
'結合セル対応
If sh.Cells(lastRow, targetCol).MergeCells Then
lastRow = lastRow + sh.Cells(lastRow, targetCol).MergeArea.Rows.Count - 1
End If
GetLastRowNum = lastRow
End Function
コードの説明
lastRow = sh.Cells(Rows.Count, targetCol).End(xlUp).Row
エクセルの最終行からCtrl+↑を押下した時に移動するセルの行番号を返します。
If sh.Cells(lastRow, targetCol).MergeCells Then
lastRow = lastRow + sh.Cells(lastRow, targetCol).MergeArea.Rows.Count - 1
End If
取得した最終行番号のセルが結合しているかチェックする。
結合している場合は、結合している行数-1を取得した最終行番号に加算する。
結合セル部分の説明
A10～A12が結合されている場合に、メソッドの前半部分だけを実行すると、
Sub test()
Dim sh As Worksheet
Set sh = ThisWorkbook.Worksheets("Sheet1")
Debug.Print GetLastRowNum(sh, 1)
End Sub
Function GetLastRowNum(ByVal sh As Worksheet, ByVal targetCol As Long) As Long
Dim lastRow As Long
lastRow = sh.Cells(Rows.Count, targetCol).End(xlUp).Row
GetLastRowNum = lastRow
End Function
結果は10になります。
結合セルに対して「row」プロパティで行番号を取得すると一番上の行番号が返されるからです。
If sh.Cells(lastRow, targetCol).MergeCells Then
lastRow = lastRow + sh.Cells(lastRow, targetCol).MergeArea.Rows.Count - 1
End If
結合されている行数-1を加算しているのは、元から結合セル内のもっとも上の行番号がrowに格納されているからです。
上記例だと-1しないと、10+3で13が戻り値になってしまいます。
最終列番号を取得するメソッド
'引数①のシートの引数②の行の最終入力列番号を返す
'
'@param sh 対象のワークシート
'@param targetRol 対象列
'@return Long 最終列から左方向に見て最初に入力のあるセルの列番号
Function GetLastColumnNum(ByVal sh As Worksheet, ByVal targetRow As Long) As Long
Dim lastColumn As Long
lastColumn = sh.Cells(targetRow, Columns.Count).End(xlToLeft).Column
'結合セル対応
If sh.Cells(targetRow, lastColumn).MergeCells Then
lastColumn = lastColumn + sh.Cells(targetRow, lastColumn).MergeArea.Columns.Count - 1
End If
GetLastColumnNum = lastColumn
End Function
行の場合と仕組みは同じです。
E1～G1が結合されている1行目を対象に実施すると、7が返されます。
まとめ
ユーザが手作業で操作するブックを集計する時などに結合セルがあってハマることがあると思います。
最終行列を取得する処理は、エクセルVBAを使っているとよく使うのでメソッドにしておいて、パッと出せると便利になると思います。
間違いやアドバイス等があればコメントや編集リクエストください。