和風スパゲティのレシピ

日本語でコーディングするExcelVBA

トップ > 汎用関数集 > 最終行/最終列を取得する万能関数を作る

最終行/最終列を取得する万能関数を作る

汎用関数集 Range・Cells プロシージャ分割 VBA

ExcelVBAでマクロを作るなら、最終行(最終列)の取得は必須コードといっていいくらいですが、ひと口に最終行と言っても、取得方法はいろいろなやり方があります。

シートのレイアウトや、オートフィルターやテーブルの有無など、
どの最終行を取るべきなのかが、マクロごとに違いますからね。


代表的なところでは、UsedRange、End、Range、CurrentRegion、AutoFilterなどのプロパティ・オブジェクトを活用するコードが挙げられ、
実際のコードは、こちらの記事で詳しく解説しました。

www.limecode.jp


さて、この記事の中でも触れましたが、
こういうよくある処理は、関数化しておくと便利です。


なので本サイトでは、

Function GetLastR_シート
Function GetLastR_指定列
Function GetLastR_セル範囲
Function GetLastR_値あり

みたいに汎用関数集を用意して、

RLast自動メンバー

こうやって選んで使う方法をよく紹介しています。


ということで最終行取得も同じように汎用関数集にしてもいいのですが、
最終行の取得は中身のコードがすごく単純ですからね。


せっかくなので、関数を選ぶ手間も省いてしまうために、
1本で何でもやってくれる万能関数を作ってしまいましょう。


処理の手順をざっくり書くと、

  1. いろいろな種類のオブジェクトを渡せる関数にする
  2. 渡されたオブジェクトから、そのセル範囲を取得する
  3. セル範囲の最終行を返り値として返す

という関数にします。


これに加えて、

  • 列番号を指定するしないを選べる(引数を省略可能に)
  • 列指定がないときは、セル範囲の最終行をそのまま返す
  • 列指定があったときは、その列の入力最終行(値のある最後の行)を返す

というオプションを追加します。


作成した関数がこちらになります↓

ソースコード

' 最終行の取得
Function GetLastR(指定オブジェクト As Variant, Optional ByVal C As Long = -1) As Long

    ' 渡されたオブジェクトからセル範囲を取得
    Dim 対象セル範囲 As Range
    Select Case TypeName(指定オブジェクト)
    Case "Range"
        If 指定オブジェクト.Cells.CountLarge = 1 Then ' 単独セルにはCurrentRegionを取る
            Set 対象セル範囲 = 指定オブジェクト.CurrentRegion
        Else
            Set 対象セル範囲 = 指定オブジェクト
        End If
    Case "Worksheet"
        Set 対象セル範囲 = 指定オブジェクト.UsedRange
    Case "AutoFilter", "ListObject"
        Set 対象セル範囲 = 指定オブジェクト.Range
    Case Else
        Err.Raise 1000, , "対象外のオブジェクト「" & TypeName(指定オブジェクト) & "」が指定されました。"
    End Select
    
    ' エリアの最終行を取得
    GetLastR = 対象セル範囲.Rows.Count + 対象セル範囲.Row - 1
    
    ' 列が指定されていればその列の入力最終行を取得
    If C <> -1 Then
        Do While 対象セル範囲.Worksheet.Cells(GetLastR, C) = ""
            GetLastR = GetLastR - 1
            If GetLastR < 対象セル範囲.Row Then
                GetLastR = 0
                Exit Function
            End If
        Loop
    End If

End Function

' 最終列の取得
Function GetLastC(指定オブジェクト As Variant, Optional ByVal R As Long = -1) As Long

    ' 渡されたオブジェクトからセル範囲を取得
    Dim 対象セル範囲 As Range
    Select Case TypeName(指定オブジェクト)
    Case "Range"
        If 指定オブジェクト.Cells.CountLarge = 1 Then ' 単独セルにはCurrentRegionを取る
            Set 対象セル範囲 = 指定オブジェクト.CurrentRegion
        Else
            Set 対象セル範囲 = 指定オブジェクト
        End If
    Case "Worksheet"
        Set 対象セル範囲 = 指定オブジェクト.UsedRange
    Case "AutoFilter", "ListObject"
        Set 対象セル範囲 = 指定オブジェクト.Range
    Case Else
        Err.Raise 1000, , "対象外のオブジェクト「" & TypeName(指定オブジェクト) & "」が指定されました。"
    End Select
    
    ' エリアの最終列を取得
    GetLastC = 対象セル範囲.Columns.Count + 対象セル範囲.Column - 1
    
    ' 列が指定されていればその列の入力最終行を取得
    If R <> -1 Then
        Do While 対象セル範囲.Worksheet.Cells(R, GetLastC) = ""
            GetLastC = GetLastC - 1
            If GetLastC < 対象セル範囲.Column Then
                GetLastC = 0
                Exit Function
            End If
        Loop
    End If

End Function

実行例

最終行取得関数の実行例

↑この通り、ひとつの関数で複数のオブジェクトを処理できる関数です。

オプションもちゃんと動いていて、指定列を渡したときだけ、
空セルを除外して「入力」最終行を求める関数になっています。

コードの解説

  1. いろいろな種類のオブジェクトを渡せる関数にする
  2. 渡されたオブジェクトから、そのセル範囲を取得する
  3. セル範囲の最終行を返り値として返す

この部分は、TypeName関数を使って実装しています。
　

MsgBox TypeName(Worksheets("データ"))

↑これで「Worksheet」を返してくれる関数ですので、
あとは普通にSelect Caseで文字列を判定して分岐しているだけですね。


また、渡されたRangeオブジェクトが単独のセルだった場合、
そのCurrentRegionを取ってから最終行を取得しています。


便利！と思ったら採用してください。

うーんこういう暗黙の仕様嫌い…って方は不採用にして、
該当箇所のIfステートメントを削除して使ってください。

　

  • 列番号を指定するしないを選べる(引数を省略可能に)
  • 列指定がないときは、セル範囲の最終行をそのまま返す
  • 列指定があったときは、その列の入力最終行(値のある最後の行)を返す

こちらの仕様は、引数を省略可能にするOptionalキーワードを使って実装しています。


Optional実装時のコツとして、
省略時の値は「-1」などあまり使わない値にします。

Optional ByVal C As Long = 0
' ↓
If C <> 0 Then

という実装にすると、誤って0を渡してしまったとき、列を渡さなかったと判断される恐れがあるからです。

空のセルを参照するなど、誤って0を渡す可能性は結構ありますが、
セルの番地に-1を渡すミスはめったにないという狙いですね。


ついでですが、最終行取得にメジャーなEndプロパティさんはクビになりました。

この罠があるからです↓

www.limecode.jp


Endさんは「特定の列の最終行取得を短いコードで書くのが得意」ですが、
この汎用関数を作ればもっと短い「GetLastR」で全部済みますからね。


一度作りさえすれば！という汎用関数づくりには、
コードの短さは全くメリットになりません。

使い倒すなら何より安全性が大事です。


サボるための努力をサボらないように気をつけましょう。

汎用関数の実行速度について

こういった汎用性の高い関数の話をしたとき、

「これだと遅くならないですか？」

という質問をたまにいただきます。


ストレートに「ワークシート.UsedRange」を実行したときと比べ、

オブジェクトの種類を調べて分岐したり、
指定列が省略されたか判定したりしていますから、

確かにちょっと心配になる気持ちはわかります。


が、結論まったく気にする必要はありません。

何故なら「重い処理」でもなく「何万回も実行する処理」でもないからです。



プログラムの処理速度を考える上では、

「すべての処理を速くしなくていいから、最も遅い処理をとにかく速くする」

ことを意識するのが重要になります。


セル1個ずつの転記をセル範囲ごとのコピペや配列処理に変えたり、
検索処理をバイナリサーチで実装したりするのはとても大切です。

対して、
「Worksheets("データ")よりWorksheets(1)が速い」
「IntegerよりLongが速い」
みたいなうんちくは、偉そうに語る人もいますがとにかく無視してOKです。


IfやSelect Caseの比較なんて、億回やっても大したことありませんからね。

とにかく読みやすく書いて、コードを書く時間を減らした方が高速なマクロです。


汎用関数で分岐をたくさん使うのは気にしなくていいので安心してください。



ということで、最終行/最終列を取得する万能関数の紹介を終わります。

これさえあれば「GetLastR」「GetLastC」と書くだけで最終行が取得できます。
是非ご活用ください。


汎用関数づくりに興味のある方はこちらの記事もどうぞ

www.limecode.jp

おまけ：ポリモーフィズム（多態性）とは

オブジェクト指向の3大要素に「ポリモーフィズム」というのがあります。

偉い人が怒りそうな説明をすると、ちょうど今回やった
「渡したオブジェクトの種類で動きを変えてくれる関数」のことです。


この記事を読んだ今がこの用語を一番覚えやすいタイミングだと思いますので、
せっかくなので覚えてしまいましょう。


今回の関数GetLastRは、

GetLastR(ws集計表)
GetLastR(ws集計表.AutoFilter)
GetLastR(ws集計表.AutoFilter, 4)
GetLastR(ws集計表.ListObject(1))
GetLastR(ws集計表.Range("G3"))

と、いろいろなオブジェクトを渡しても中でいい感じに処理をして、
最終的には「最終行を返す」という統一した動きをしてくれます。

これをポリモーフィズムと呼ぶということですね。


仰々しいオブジェクト指向の説明を読むとかなりわかりづらい用語ですが、
実際の関数を見ると、なんてことはない概念です。


ただし、厳密に言うとこれはポリモーフィズムの目的です。


ポリモーフィズムを実現するための手段の話をすると、
今回のように「TypeNeme関数で愚直に分岐」するのではなく、

Function GetLastR(ワークシート As Worksheet) As Long

Function GetLastR(セル範囲 As Range) As Long

Function GetLastR(セル範囲 As Range, C As Long) As Long

Function GetLastR(テーブル As ListObject) As Long

Function GetLastR(オートフィルター As AutoFilter) As Long

と、同じ名前だけど引数の型が違う関数をたくさん作れるという機能が、
本物のオブジェクト指向プログラミング言語には備わっています。


実際に使うときは、渡した引数によってどのプロシージャを呼ぶのかを、
コンピュータさんが選んでくれるのです。

おー便利！ExcelVBAにもこれ欲しいですね～


ということで、厳密には「この手段も合わせてポリモーフィズム」らしいので、
今日作ったGetLastRさんは残念ながら多能性があるとは言えません。



このように、ExcelVBAはオブジェクト指向言語ではありませんので、
ポリモーフィズムを実現する手段は無いです。


ですが今回のように、目的を無理やり果たすのは、
実はそんなに難しくありません。

TypeNameとSelect Caseだけで作れたわけですからね。


こんな風になんちゃってポリモーフィズムはできますので、
なにか機会があれば、作ってみてください。