最終行・最終列を取得する方法まとめ

Contents
• 今回の目標
• 最終行を取得する 　★‥おすすめ度（使いやすさ）です。
  • ★★★【取得方法①】xlUpを使用して、最終行を取得する
  • ★★☆【取得方法②】UsedRangeを使用して最終行を取得する
• 最終列を取得する　 　★‥おすすめ度（使いやすさ）です。
  • ★★★【取得方法①】xlToLeftを使用して、最終列を取得する
  • ★★☆【取得方法②】UsedRangeを使用して最終列を取得する
• 表の範囲や表の最終行・最終列を取得する 　★‥おすすめ度（使いやすさ）です。
  • ★★☆【取得方法①】CurrentRegionを使用して、表が何行／何列か取得する
  • ★★☆【取得方法②】xlDownを使用して、表の最終行を取得する
  • ☆☆☆【取得方法③】xlToRightを使用して、表の最終列を取得する
• うまく取得できない場合
  • 【原因１】最適な取得方法をしていない！
  • 【原因２】ブランク文字がある！
  • 【原因３】絞り込みをしている！
• エラーが発生した場合
  • 【原因と解決方法】オーバーフローが発生
• xls形式とxlsx/xlsm形式の違いについて
  • 【比較】最終列と最終行の数が違う！
• 関連リンク

「表の最終行まで取得したい！」や「うまく最終行を取得できない…！」と悩んでるあなたへ。 この記事では、VBAで最終行や最終列の取得方法について、サンプルを用いて丁寧に解説します！

目標リスト

• 最終行を取得できるようになる
• 最終列を取得できるようになる
• 取得できなかった場合の対処方法を知る

最終行を取得する

【取得方法①】xlUpを使用して、最終行を取得する

xlUpを使用すると、指定した列の一番下の行（xlsx/xlsm形式では、1,048,576行目）から「上方向に向かって」最初にデータがあるセルの行番号を取得します。

上記の画像ではB列のエクセルの最終行(1,048,576行目)から上方向に向かって初めてデータがある行（使用されている最終行）を取得しています。なお、取得する列にデータの入力（画像ならセルB7の「5」の入力）が漏れてしまうと最終行が正しく取得できません。

Sub EndRowSample1()
    Dim endRow As Long
    '2列目の最終行を取得する
    endRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    
    Debug.Print endRow
End Sub

【取得方法②】UsedRangeを使用して最終行を取得する

UsedRangeを使用すると、列関係なく、シートで使用している最後の行を取得します。

上記の画像の例だとB10の10行目が最終行となります。繰り返し処理の対象が表内に限られる場合は、適さないことがあります。

Sub EndRowSample2()
    Dim endRow As Long
    Dim buf As Long
    '（シートの使用している一部の範囲を取得）
    buf = ActiveSheet.UsedRange.Rows.Count
    'シート全体の使用している最後の行を取得
    endRow = ActiveSheet.UsedRange.Rows(buf).Row
    
    Debug.Print endRow
End Sub

最終列を取得する

【取得方法①】xlToLeftを使用して、最終列を取得する

xlToLeftを使用すると、指定した列の一番右の行（xlsx/xlsm形式では、XFD列／16,384列）から「左方向に向かって」最初にデータがあるセルを取得します。

Sub EndColumnsSample1()
    Dim endColumn As Long
    '2行目の最終列を取得する
    endColumn = Cells(2, Columns.Count).End(xlToLeft).Column
    
    Debug.Print endColumn
End Sub

【取得方法②】UsedRangeを使用して最終列を取得する

UsedRangeを使用すると、行関係なく、シートで使用している最後の列を取得します。

Sub EndColumnsSample2()
    Dim endRow As Long
    Dim buf As Long
    '（シートの使用している一部の範囲を取得）
    buf = ActiveSheet.UsedRange.Columns.Count
    'シート全体の使用している最後の列を取得
    endRow = ActiveSheet.UsedRange.Columns(buf).Column
    
    Debug.Print endRow
End Sub

表の範囲や表の最終行・最終列を取得する

表の範囲や表の最終行・最終列を取得する場合、表の一番左に連番を付けましょう。

【取得方法①】CurrentRegionを使用して、表が何行／何列か取得する

RangeオブジェクトのCurrentRegionを使用して、行を取得する場合は「Rows.Count」、列を取得する場合は「Columns.Count」とします。

注意点ですが、表内に結合されたセルがあると誤った範囲を取得してしまう場合があるようです。Excel2016以降は、ほぼ起こらなくなったようですが、CurrentRegionを使用する際は注意しましょう。
※条件付き書式やフィルターが設定された行などでも、意図しない範囲が取得される場合があります。

Sub RangeSample1()
    Dim numRow As Long
    Dim numCol As Long
    'B2にある表の行をカウントする
    numRow = Range("B2").CurrentRegion.Rows.Count
    'B2にある表の列をカウントする
    numCol = Range("B2").CurrentRegion.Columns.Count
    '取得した表の行と列を表示する
    Debug.Print "numRow：" & numRow
    Debug.Print "numCol：" & numCol
End Sub

【取得方法②】xlDownを使用して、表の最終行を取得する

xlDownを使用すると、指定したセルから「下方向に向かって」データが連続している最後の行番号を取得します。

注意点が２つあります。
１つ目は、確認する下の列に「結合されたセル」があると、そのセルで止まってしまうため、確認する列には結合セルを使用しないでください。
２つ目は、データが連続していない場合は、エクセルの最終行（xlsx/xlsm形式では、1,048,576行目）を取得してしまうため、最終行の取得した値がエクセルの最終行になっていないか確認しましょう。

Sub RangeSample2()
    Dim endRow As Long
    Dim pos As String
    
    'B2の下は何行目まで続くかを取得する
    pos = "B2"
    endRow = Range(pos).End(xlDown).Row

    'B2より下にデータがない場合、エクセルの最下行になるため訂正する
    If (Rows.Count = endRow) Then
        endRow = Range(pos).Row
    End If
End Sub

【取得方法３】xlToRightを使用して、表の最終列を取得する

xlToRightを使用すると、指定したセルから「右方向に向かって」データが連続している最後の列番号を取得します。

注意点が２つあります。
1つ目は、確認する右の列に「結合されたセル」があると、そのセルで止まってしまうため、確認する行には結合セルを使用しないでください。
2つ目は、データが連続していない場合は、エクセルの最終列（xlsx/xlsm形式では、XFD列／16,384列）を取得してしまうため、最終列の取得した値がエクセルの最終列になっていないか確認しましょう。

Sub RangeSample3()
    Dim endColumn As Long
    Dim pos As String
    
    'B2の下は何行目まで続くかを取得する
    pos = "B2"
    endColumn = Range(pos).End(xlToRight).Column
    
    'B2より右にデータがない場合、エクセルの最終列になるため訂正する
    If (Columns.Count = endColumn) Then
        endColumn = Range(pos).Column
    End If
    
    Debug.Print endColumn
End Sub

うまく取得できない場合

【原因１】最適な取得方法をしていない！

適していない方法で最終行・最終列を取得すると、正しく出来ない場合があります。

【xlUp・xlToLeft】を使用している場合

表の外側にデータがあると、そのデータにぶつかってしまう場合があります。
調べる行の下、列の右にはデータを記載しないようにしましょう。

【xlDown・xlToRight】を使用している場合

取得する列（行）でセルの結合をしていると、結合セルの先頭行や列の位置が取得されてしまいます。
セルの結合を使用しない構成にするか、【xlUp・xlToLeft】に切り替えましょう。

【UsedRange】を使用している場合

ゴミのデータが残っていて実際の最終行・最終列より多く取得する場合があります。
使用していない行と列を削除して、保存し、再度開くと直る場合がありますが、 それでも修正されない場合は【xlUp・xlToLeft】へ切り替えを検討しましょう。

【CurrentRegion】を使用している場合

表内でセルの結合をしていると、うまく取得できない場合があります。
セルの結合を使用しない構成にするか、【xlUp・xlToLeft】へ切り替えを検討しましょう。

【原因２】ブランク文字がある！

一見、文字が無いように見えても、ブランクの文字がセルに入っている場合があります。これは半角スペースや全角スペースとは異なります。
主に、Excel関数のIF文で「=IF(A1="有", "A", "")」のようなブランク文字を『値貼り付け』した時に生じます。
このブランク文字は、マクロで簡単に削除することが出来ます。

'ブランク文字を削除する
Sub Delete_BlankChar()
    Dim cell As Range
    Dim bl1 As Boolean
    Dim bl2 As Boolean

    'アクティブなシートの使用されているセル全てが処理対象
    For Each cell In ActiveSheet.UsedRange
        'セルがブランク若しくはブランク文字か
        bl1 = cell.Value = ""
        'セルがExcel関数によるブランクでないか
        bl2 = Len(cell.Formula) = 0
        '両方の条件が真の時、セルをクリアして空にする
        If bl1 And bl2 Then
            cell.ClearContents  
        End If
    Next cell
End Sub

【原因３】最終行や最終列が非表示になっている！

xlUp や xlDown、xlToLeft、xlToRight を使用した場合、表示されているセルの範囲内で、最終行や最終列が取得されます。 つまり、最終行や最終列が非表示になっていると正しく取得できません。また、フィルターの絞り込み、グループ化の折りたたみにより、非表示になっている状態でも、正しく取得できません。
最終行・最終列を取得する前に、行・列を全て表示させましょう。
※サンプルプログラムに非表示を解除するプログラムを載せました。

'全ての行・列を表示させる
Sub ShowAllRowsColumns()
    Cells.EntireRow.Hidden = False
    Cells.EntireColumn.Hidden = False
End Sub

エラーが発生した場合

【原因と解決方法】オーバーフローが発生

取得する変数の型をLong型に訂正する

「オーバーフロー」とは、変数の格納できる上限を超えた値を格納しようとした時に発生するエラーです。
エクセルの行の最大数は、xlsx, xlsm形式なら「1,048,576行」、xls形式なら「65,536行」です。
Integer型の変数は約3万までしか扱えないため、オーバーフローが生じる可能性があります。
約20億まで扱えるLong型を使用してください。

　リンク：各型の保存可能な値について

Sub EndRowSampleEx()
    Dim endRow As Long
    '2列目の最終行を取得する
    endRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
    Debug.Print endRow
End Sub

xls形式とxlsx/xlsm形式の違いについて

【比較】最終列と最終行の数が違う！

Excelでは、使用するファイル形式（xls形式とxlsx/xlsm形式）で最終行や最終列の数が異なります。
xls形式は、主にExcel97～Excel2003で使用されていた古い形式で、xlsx/xlsm形式はExcel2007以降の標準的な形式です。
xls形式は、xlsx/xlsm形式より、セル数が少なく（下記の表を参照）、ファイル容量が大きくなりがちなうえに、セキュリティ面でも劣るため、現在では推奨されていません。
xls形式は、旧ソフトウェアのための下位互換で残されているためだけに残されています。原則、xlsx/xlsm形式を使用しましょう。

行と列のセルの上限

余談ですが、「新規作成」でブック作成後、xls形式で保存すると、一時的にセルの上限が xlsx, xlsm形式と同じ（1048576行 × 16384列）になります。 しかし、再度ブックを開くとxls形式の上限（65536行 × 256列）に自動的に切り替わります。 この時、上限を超えていたセル（例：A65537）に入力されていたデータは自動的に削除されてしまいます。

'プロシージャ名以外、後述の「GetEndRowColumn97」と同じです！
Sub GetEndRowColumn2007()
    Debug.Print "Rows.Count：" & Rows.Count
    Debug.Print "Columns.Count：" & Columns.Count
End Sub

'プロシージャ名以外、先述の「GetEndRowColumn2007」と同じです！
Sub GetEndRowColumn97()
    Debug.Print "Rows.Count：" & Rows.Count
    Debug.Print "Columns.Count：" & Columns.Count
End Sub

• 【実行時エラー13】型が一致しません。

• 変数と型について