VBA技術解説
最終行・最終列の取得方法(End,CurrentRegion,SpecialCells,UsedRange)

ExcelマクロVBAの問題点と解決策、VBAの技術的解説
最終更新日:2019-10-08

最終行・最終列の取得方法(End,CurrentRegion,SpecialCells,UsedRange)


エクセルの表をマクロVBAで扱う時は、
データ部分の先頭から最終行までの、開始列から最終列まで処理する事が多いでしょう。


開始行や開始列は、ほとんどの場合、見出し行や見出し列の次からになります。

単純な話として、
1行目に見出しがあれば、2行目から
1列目に見出しがあれば、2列目から

では、ここで、最終行や最終列は、どのように判断したら良いでしょうか。

列数は固定の場合が多いと思われますが、データ行数は決まった行数ではない事が普通です。

では、最終行はどのように取得すればよいでしょうか。

以下の表で説明します。

Excel VBA 解説

マクロでは、A2~A11まで処理したいですよね。

そして、この行数が増えても、マクロは変更すること無く使えるようにしたいです。

最終行取得の基本:手動ではCtrl + ↑、VBAではCells(1, 1).End(xlDown)

手操作で、A列の最終行に移動する場合は、

A1セルが選択されている状態で、
Ctrl + ↓
これで、A11に移動できます。

これをマクロで書くと、
Cells(1, 1).End(xlDown)
になります。

しかし、途中に空欄のセルがある場合、

Excel VBA 解説

この場合は、Ctrl + ↓ではA6セルになってしまいます。

このような場合は、シートの最終行(シートの一番下)のセルに移動してから、
Ctrl + ↑
とやれば、A11に移動できます。

これをマクロVBAで書くと、
Cells(Rows.Count, 1).End(xlUp)
になります。

Rows.Count
は、シートの行数になります。
2007以降なら1048576です、2003なら65536です。

つまり、シートのA列の最終行から、上に向かって、Ctrl+↑で移動している訳です。

このEndは、Rangeオブジェクトのプロパティです

End(Direction)
Directionは、
xlDown : 下へ
xlToLeft : 左へ
xlToRight : 右へ

xlUp : 上へ


ただし、

Cells(1, 1).End(xlDown)

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

これは、最期のセルを表しています、つまり最期のセルのRangeオブジェクトになります。

その行数(行位置)を取得するには、

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

Cells(Rows.Count, 1).End(xlUp).Row

このように、.Rowを指定します。
これで、データの最後の行数を取得できます。

この、.Rowは、Rangeオブジェクトのプロパティで、その行位置を返します。

上記例なら、11が返されます。

返されるとは、つまりは、
Cells(1, 1).End(xlDown).Row11と同じだと言う事です。

とにかく、最終行を取得する場合は、

Cells(1, 1).End(xlDown).Row
または
Cells(Rows.Count, 1).End(xlUp).Row

これは、定型文として丸暗記して下さい。
通常は、
Cells(Rows.Count, 1).End(xlUp).Row
これを使用します。


※注意点
表の最下行が、非表示行またはフィルタで絞り込まれて非表示になっている場合
手動のCtrl+↑でも同じですが、可視セル(見えているセル)の最終行となってしまいます。

このような場合は、全行表示およびフィルタの絞り込みを解除してから最終行の取得をしてください。
そのような方法ができない場合は、下記の別の方法を使用してください。

最終列の取得

最終行取得と同様の考えで、最終列の取得も行います。

A1セルから、Ctrl + →

シートの最終列(シートの一番右)のセルから、Ctrl + ←

マクロVBAコードで書くと

Cells(1, 1).End(xlToRight).Column
または
Cells(1, Columns.Count).End(xlToLeft).Column

最終行取得と合わせて、良く使われていますので覚えておいてください。

特殊な表の場合

上記のような手法が使えないような表の場合があります。

以下の表で説明します。

ExcelマクロVBAサンプル

最終列は見出し行の2行目で判断すれば良いでしょう。

Cells(2, Columns.Count).End(xlToLeft).Column

しかし、最終行の取得方法が、今までの方法ではうまく行きません。

マクロVBAで処理するなら、

10行目までか、12行目までになるでしょう。

しかし、

Cells(Rows.Count, 2).End(xlUp).Row
これでは、9になってしまいます。

Cells(Rows.Count, 3).End(xlUp).Row
これなら、10になりますが、

そもそも何列目に最終行のデータが入っているかが不明です。

このような場合に、使用するのは、


・CurrentRegion
・SpecialCells(xlCellTypeLastCell)
・UsedRange

・Findメソッド

これらを使用します。

CurrentRegion

CurrentRegionプロパティを使用して最終行を取得する場合です。

Range("B2").CurrentRegion.Item(Range("B2").CurrentRegion.Count).Row
または、
Range("B2").CurrentRegion.Row + Range("B2").CurrentRegion.Rows.Count - 1
これでは見ずらいので、Withでまとめると
With Range("B2").CurrentRegion
  最終行 = .Item(.Count).Row
End With

または、
With Range("B2").CurrentRegion
  最終行 = .Row + .Rows.Count - 1
End With


CurrentRegionは、アクティブセル領域を返します。
最初のコードは、アクティブセル領域の最後のセルの行位置を取得しています。
次のコードは、アクティブセル領域の開始行位置とアクティブセル領域の行数を足しています。
ただし、CurrentRegionなので、途中に空白行(何も入っていない行)がある場合は、
そこまでの行になってしまいます。

SpecialCells(xlCellTypeLastCell)

SpecialCellsプロパティを使用して最終行を取得する場合です。

Cells.SpecialCells(xlCellTypeLastCell).Row

SpecialCells(xlCellTypeLastCell)は、シートの最終セルを返します。
つまり、シートの使っているセルの最終位置を取得しています。
上の表なら、12となります。
(表範囲以外には何のデータもはいっていないとしてです。)

※SpecialCellsプロパティの範囲について
罫線が引いてあればそこまで範囲に入ってしまうのはもちろんですが、
行高を規定高から変更しているだけでも、その行まで範囲に入ってしまいます。
つまり、シートの下の方で行高だけを変更していても、その行まで範囲に含まれてしまうという事です。
従って、注意しないと大きな範囲を処理するVBAとなってしまい、処理速度のとても遅いマクロになってしまいます。

UsedRange

UsedRangeプロパティを使用して最終行を取得する場合です。

ActiveSheet.UsedRange.Item(ActiveSheet.UsedRange.Count).Row
または
ActiveSheet.UsedRange.row + ActiveSheet.UsedRange.Rows.count - 1
これでは見ずらいので、Withでまとめると
With ActiveSheet.UsedRange
  最終行 = .Item(.Count).Row
End With

または、
With ActiveSheet.UsedRange
  最終行 = .Row + .Rows.Count - 1
End With


UsedRangeは、指定されたワークシートで使われたセル範囲を返します。
使われたセル範囲の開始行位置と使われたセル範囲の行数を足しています。
UsedRangeは、シートの指定が必須となります。
ただし、
以下の注意点を良く確認してください、使うには少々注意が必要です。

※UsedRangeプロパティの範囲について
罫線が引いてあればそこまで範囲に入ってしまうのはもちろんですが、
行高を規定高から変更しているだけでも、その行まで範囲に入ってしまいます。
さらに、行を非表示行にしていても、その非表示行が範囲に含まれてしまいます。
つまり、
シートの下の方で行高だけを変更していても、罫線を引いていても、非表示にしていても、
これら、規定の状態から変更しているだけで、その行まで範囲に含まれてしまうという事です。
従って、注意しないと大きな範囲を処理するVBAとなってしまい、処理速度のとても遅いマクロになってしまいます。

※注意してください
UsedRangeについて間違った解説を載せているサイトを見かけます。
「エクセル VBA」でググってトップの方に表示されるようなサイトでも以下のVBAコードがでている場合がありますです。

ActiveSheet.UsedRange.Rows.count

これで最終行の取得としているのです。
これでは、上記の表の場合、11となってしまいます。
つまり、UsedRangeなので、
未使用の行が先頭にある場合は、その行数が含まれなくなってしまうのです。

Findメソッド

RangeオブジェクトのFindメソッドを使います。
Findメソッドは、VBAでセル範囲内の条件に当てはまるセルを検索するものです。Findメソッドは、Rangeオブジェクトのメソッドで、ワークシート操作の「検索と置換」の「検索」の機能をVBAで使うものです。上の画像では「検索する文字列」だけしか指定できませんが、「オプション」をクリックすると、

最終行 = ActiveSheet.Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
名前付き引数を使って書くと、
最終行 = Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

とにかく最後の文字・数値・数式等が入っているセルを見つけようという事です。
あらゆる文字("*")を下から上へ(xlPrevious)へ探しています。
行方向なので(xlByRows)、列なら(xlByColumns)になります。
CellsはUsedRangeでも良いです。

非表示行も対象となりますので汎用性はあります。
ただし、複数の表(別々の連続セル範囲)があるシートでは、表を指定することはできません。
このような場合はCurrentRegionを使ってください。
また、Findメソッドは指定オプションがシート操作とリンクしている点については注意してください。

サイト内関連ページ

第18回.最終行の取得(End,Rows.Count)
Excelワークシートにおける表の最終行の取得は、VBAの必須技術になります、エクセルVBAにおける最終行取得の必要性 エクセルは表計算ソフトです、つまり縦横の表を扱います、データは横に項目があり、縦に項目に対するデータが入っている事が一般的です。しかし、そのデータ行数は決まった行数ではない事が普通です。

第6回.表の先頭から最終行まで繰り返す(ForとEnd(xlUp).Row)
エクセルの表は、横の項目はあまり変更はないものですが、縦のデータ数、つまり行数は常に変わるものです、前回のように、固定の回数しか繰り返せないのでは困ります、データ数に応じて、最終行まで繰り返す方法が必要です。データの最終行が分かれば良いのです。

Rangeの使い方:最終行まで選択を例に
Rangeの使い方・書き方について、データ最終行まで選択する場合を例に説明します、Rangeの書き方なので、RangeオブジェクトではなくRangeプロパティの解説という事になります。最近続けざまに、以下のようなコードを見かけました。Range("A2",Range("A2").End(xlDown)).Selec…

最終行の判定、Rangeオブジェクトと配列、高速化の為に
最終行の判定 エクセル顧客管理の記事からのスピンオフ記事になります。以前に、モジュール「顧客一覧へ登録」において、.Cells.SpecialCells(xlLastCell).End(xlUp).Row と Cells(Rows.Count,1).End(xlUp).).Row を紹介しましたが、



同じテーマ「VBA技術解説」の記事

RangeとCellsの深遠
Offset、Resizeを使いこなそう
値渡し、参照渡しについて(ByVal,ByRef)
最終行・最終列の取得方法(End,CurrentRegion,SpecialCells,UsedRange)
ユーザー定義関数の作り方
セルの値について(Value,Value2,Text)
Excelのバージョンを判断して「名前を付けて保存」
空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)
空白セルを正しく判定する方法2
Rangeオブジェクト.Valueの省略について
ユーザー操作を制限する(Locked,Protect,ScrollArea)


新着記事NEW ・・・新着記事一覧を見る

トランザクション処理|SQL入門(12月11日)
インデックスを作成して高速化(CREATE INDEX)|SQL入門(12月9日)
他のテーブルのデータで追加/更新/削除|SQL入門(12月8日)
データの削除(DELETE)|SQL入門(12月7日)
データの更新(UPDATE)|SQL入門(12月6日)
複数のSELECT結果を統合(UNION,UNION ALL)|SQL入門(12月5日)
テーブルを結合して取得(INNER JOIN,OUTER JOIN)|SQL入門(12月4日)
データベースの正規化とマスタの作成|SQL入門(12月3日)
データベースにおけるNULLの扱い方|SQL入門(12月2日)
オブジェクト変数とは何か|VBA技術解説(12月2日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|VBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.繰り返し処理(For Next)|VBA入門
7.マクロって何?VBAって何?|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説
10.ひらがな⇔カタカナの変換|エクセル基本操作



  • >
  • >
  • >
  • 最終行・最終列の取得方法(End,CurrentRegion,SpecialCells,UsedRange)

  • このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


    記述には細心の注意をしたつもりですが、
    間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
    掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
    掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



    このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
    本文下部へ