VBAで最終行・最終列を取得する方法 | システムエンジニアライフ

VBAで最終行・最終列を取得する方法

ヘッダー広告
スポンサードリンク

今回はEXCELのVBAでデータの最終行や最終列を取得する方法についての記事となります。

データというのは常に増減するものなので、最終行が変わり続けることがほとんどです。
そのため最終行が変動しても常に最終行を判断できるロジックとする必要があります。

今回は、変動する最終行と、おまけで最終列も取得する方法についても作成したいと思います。

EXCELデータの最終行・最終列の取得方法

上のデータから下に最終データを検索する方法

ではサンプルデータを見てみましょう。


データ参考:全国地方自治体所在地(住所)一覧&公式サイトリンク集 http://advance21.sakura.ne.jp/chihoujichitai/

このデータの最終行である16行目のNo.15 杉並区のデータを取得したいとします。
以下はそのデータを取得するためのロジックになります。

Public Sub GetEndRowColmn()
    
    Dim GovSheet As Worksheet
    
    
    Set GovSheet = ThisWorkbook.Worksheets("自治体一覧")
    
    With GovSheet
        
        'A1セルからデータの最終行を取得
        Debug.Print "最終行データ:" & .Cells(1, 1).End(xlDown)
        
        'A1セルからデータの最終列を取得
        Debug.Print "最終列データ:" & .Cells(1, 1).End(xlToRight)
        
    End With

End Sub

出力結果
最終行データ:15
最終列データ:電話番号

●解説

最終行を取得する

Worksheet.Cells(1, 1).End(xlDown)

cellsで指定したセルからCtrl + ↓(下矢印キー)をクリックした時と同じ動きをします。

最終列を取得する

Worksheet.Cells(1, 1).End(xlToRight)

cellsで指定したセルからCtrl + →(右矢印キー)をクリックした時と同じ動きをします。

データの最終位置を取得する際には、上記のようにCells.End(~)を指定することで可能となりますので参考にしていただければと思います。

ちなみにこういった方法で取得することも可能です。

Public Sub GetEndRowColmnLoop()
    
    Dim GovSheet As Worksheet
    Dim i As Long
    
    Set GovSheet = ThisWorkbook.Worksheets("自治体一覧")
    
    With GovSheet
        
        i = 0
        Do Until .Cells(1 + i, 1) = ""
            i = i + 1
        Loop
        
        'A1セルからデータの最終行を取得
        Debug.Print "最終行データ:" & .Cells(1, 1).End(xlDown)
        
    End With

End Sub

このロジックはループ処理をしながら空白チェックを行っていく方法となります。
全てのデータを取得する必要がある場合には、このようにループ処理をしている最中に終了判定でループを終えるということもたまに使います。

シートの最終行から上に最終データを検索する方法

先ほどA1セルから下に移動して最終行を取得する方法をお伝えしましたが、この処理の方法だとデータの内容によっては、問題が発生する可能性があります。

以下のデータに対して処理を行ってみます。


最初のデータとの違いは、データの途中に空白の行が存在するかどうかというものになります。
今回のデータは空白が存在しています。

こちらのデータに対して、『Worksheet.Cells(1, 1).End(xlDown)』にて処理を行った結果は以下の通りとなります。

出力結果
最終行データ:12
最終列データ:電話番号

このように途中で空白が存在する場合には、最終行まで移動せず、空白の直前までしか移動しません。
こういったデータに対して最終行を取得する方法は以下のロジックとなります。

Public Sub GetEndRowColmn()
    
    Dim GovSheet As Worksheet
    
    
    Set GovSheet = ThisWorkbook.Worksheets("自治体一覧")
    
    With GovSheet
        
        '最終行から上に検索し、最初にデータがある行(データの最終行)を取得
        Debug.Print "最終行データ:" & .Cells(.Rows.Count, 1).End(xlUp)
        
        '最終列から左に検索し、最初にデータがある列(データの最終列)を取得
        Debug.Print "最終列データ:" & .Cells(1, .Columns.Count).End(xlToLeft)
        
    End With

End Sub

出力結果
最終行データ:15
最終列データ:電話番号

●解説

空白が存在するデータの最終行を取得する

Worksheet.Cells(worksheet.Rows.Count, 1).End(xlUp)

worksheet.Rows.Countではシート内の最終行のカウントを返却します。
私が使っているPCには、EXCEL2016がインストールされているのですが、EXCEL2016では『1048576』が最終行として返却されます。
ちなみに、EXCEL2007以降は行数が1048576となりましたが、EXCEL2003以前は65536行でした。

たまに以下のようなロジックを書く人がいますが、EXCELバージョンアップによって行数が変更となったり、そもそもマジックナンバーを使うのはあまり良いとは言えないので、Rows.Countで取得する方がいいでしょう。

Worksheet.Cells(1048576, 1).End(xlUp)

最終列の取得は以下の書き方になります。

Worksheet.Cells(.Columns.Count, 1).End(xlToLeft)

ちなみに最初にループ処理で最終行を取得する方法を書きましたが、~行までは必ず取得する。ただし空白行は除外するという約束を握れば(要件を握れば)、以下のような記述でも良いかと思います。

Public Sub GetEndRowColmnLoop()
    
    Dim GovSheet As Worksheet
    Dim i As Long
    
    Const END_ROW As Long = 1000
    
    Set GovSheet = ThisWorkbook.Worksheets("自治体一覧")
    
    With GovSheet
        
        i = 0
        Do Until i = END_ROW
            If .Cells(1 + i, 1) = "" Then
            Else
                '処理の内容を記述
            End If
            i = i + 1
        Loop
        
    End With

End Sub

このループ処理では、1000行までループ処理を行って、A列が空白以外の行を処理していくというものになります。
私はこのような処理で記載することも多くあります。
このロジックで重要なところは、何行まで処理を行うのかをきちんと決める。空白の判断をする列を明確にする。という点になります。

この処理方法の良いところは、空白が存在していても問題なく動作するということと、処理する行を指定することで無駄な処理を減らすということになります。
是非使ってみてください。

以上がEXCEL VBAで最終行や最終列を取得する方法になります。
シートのデータ内容に応じて使うロジックを変更して、データの操作をできるようになるために参考になれば幸いです。
本日はここまで!

フッター広告

スポンサードリンク



シェアする

  • このエントリーをはてなブックマークに追加

フォローする