【ExcelVBA】データの最終列を取得する方法

と思ったこともあるのではないでしょうか。

そこで今回は、任意のシートと行を指定して最終列を取得する方法について紹介します。

コピペもできるので、使ってみてください。

VBAで最終列を取得するサンプルコード

Dim trgtSh As Worksheet
Set trgtSh = ThisWorkbook.Worksheets("サンプル")
Dim trgtLastCol As Long
trgtLastCol = trgtSh.Cells(1, trgtSh.Columns.Count).End(xlToLeft).Column

MsgBox "「" & trgtSh.Name & "」シート「1」行目の最終列は「" & trgtLastCol & "」列目です。"

上記サンプルコードを実行すると「サンプル」シートが画像のデータを保持している時、最終列「10」列目を取得、次のMsgBoxを表示します。
次に詳しい解説をしていきます。

VBAで最終列を取得するコードの解説

Dim trgtSh As Worksheet
Set trgtSh = ThisWorkbook.Worksheets("サンプル")

この部分で、最終列を取得する対象シート「サンプル」シートを変数「trgtSh」に定義しています。

Dim trgtLastCol As Long
trgtLastCol = trgtSh.Cells(1, trgtSh.Columns.Count).End(xlToLeft).Column

つづいて、最終列を変数「trgtLastRow」に定義しています。

この部分では「trgtSh.Cells(1, trgtSh.Columns.Count).End(xlToLeft)」というRangeオブジェクトから「Column」プロパティで対象セルの列数を取得しています。

ここでポイントとなるのは次の2点です。
まず「trgtSh.Columns.Count」についてですが、これは「Columns.Count」によって「trgtSh」の列数をカウントした数を返します。つまり「trgtSh」に全部で何列あるかを数値で返すので「.xlsm」ファイルの場合、画像のように「16,384（XFD列） 」を返します。

次に「End」プロパティについてですが、これは対象のセルから Ctrl を押しながら引数に指定した「方向」の 矢印キー を押した結果のセルを返します。

サンプルコードでは引数に「xlToLeft」つまり「←」方向を指定していますので対象のセルから Ctrl + ← を押した結果を返します。また、この時の対象セルは「1」行目と「trgtSh.Columns.Count」で求めた列数を指定しているので「trgtSh.Cells(1, 16384)」セル（1行目XFD列）となります。

よって「trgtSh」の「1」行目のデータがある最終セルは、Rangeオブジェクト「trgtSh.Cells(1, trgtSh.Columns.Count).End(xlToLeft)」で取得することができます。そして、このRangeオブジェクトの「Columns」プロパティから列数を取得して変数「trgtLastCol」に定義しています。

MsgBox "「" & trgtSh.Name & "」シート「1」行目の最終列は「" & trgtLastCol & "」列目です。"

最後に、取得した列数をMsgBoxで表示します。

また、今回は最終列取得の解説ですが、最終行の取得については下記を読んでください。

Endプロパティの引数

サンプルコードで使用した「End」プロパティについて、引数の一覧とそのイメージを紹介します。

VBAで最終列を取得する際の注意点と応用

データが一つもない時

今回紹介した最終列取得方法には注意点もあります。

それは「データが一つもない時」と「データが一つだけある時」の返り値が同じになってしまうことです。

データが一つでもある時と、一つもない時で処理を分けたい場合は、次のように最終列が「1」だった場合に1列目のセルが空白かどうかを判定して、処理を分岐するようにしましょう。

Dim trgtSh As Worksheet
Set trgtSh = ThisWorkbook.Worksheets("サンプル")
'最終列を取得
Dim trgtLastCol As Long
trgtLastCol = trgtSh.Cells(1, trgtSh.Columns.Count).End(xlToLeft).Column

'最終列が「1」だった場合、開始セルにデータがあるかないかを判定
If trgtLastCol = 1 Then
    If trgtSh.Cells(1, trgtLastCol).Value = "" Then
        
        'データが一つもない時の処理
        
    Else
    
        'データが一つだけある時の処理
    
    End If
End If

最終列取得処理を自作関数化する

最終列を取得する方法を紹介しましたが、最終列を取得したい場面が複数回に渡る場合、いちいち処理を書くのは手間がかかる上、可読性が低く、メンテナンスも大変です。

そこで、繰り返し使う処理を関数化して、書きやすく、可読性が高いコードに作りかえましょう。

今回の最終列を取得する処理の場合、必要なのは「対象シート」と「対象行」です。これを応用して、次のように「Worksheetオブジェクト」と「整数値」を渡して、最終列を返す関数を作ることができます。

'----------------------------------------------------------
'LastCol
'引数　　      ws, trgtRow
'返り値　      long型整数
'呼び出し元  -
'任意のワークシート、行数より、最終列を取得
'----------------------------------------------------------
Public Function LastCol(ByVal ws As Worksheet, ByVal trgtRow As Long) As Long
    Dim n As Long
    n = ws.Cells(trgtRow, ws.Columns.Count).End(xlToLeft).Column
    LastCol = n
End Function

上記の自作「LastCol」関数を使うと、最終列の取得を次のようにわかりやすく、すっきりとしたコードで書くことができます。

Dim trgtSh As Worksheet
Set trgtSh = ThisWorkbook.Worksheets("サンプル")
Dim trgtLastCol As Long
trgtLastCol = LastCol(trgtSh, 1)

また、今回は最終列取得関数を紹介しましたが、下記では最終行取得関数を紹介しているので、読んでみてください。
こうしたよく使う自作関数をまとめて一つのモジュールにしておくと、コピペで自作関数を全て実装することができるので作業効率が上がります。ぜひお気に入りの関数モジュールを作成して、使い回してみてください。

VBAで最終列を取得するまとめ

今回はVBAで任意のシート、行の最終列を取得する方法とそのポイントを紹介しました。
最終列の取得方法だけでなく、データが一つもない時の注意や、関数化して可読性を高める応用的な使い方についても理解しておくと、無駄がなく、汎用性の高いコードを書くことができるので、あわせて覚えておきましょう。