ExcelマクロVBA技術解説 | 最終行・最終列の取得方法(End,CurrentRegion,SpecialCells,UsedRange) | Excelマクロの問題点と解決策、エクセルVBAの技術的解説



最終更新日:2016-09-21

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


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

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


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

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

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

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

以下の表で説明します。



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

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

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

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

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

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



この場合は、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
これを使用します。


同様に、最終列の取得は、

A1セルから、Ctrl + →

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

マクロVBAコードで書くと

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

ここまでが基本であり、良く使われているものです。


特殊な表の場合

しかし、このような手法が使えないような表の場合があります。

以下の表で説明します。



最終列は見出し行の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


これらを使用します。


CurrentRegion

Range("B2").CurrentRegion.Item(Range("B2").CurrentRegion.Count).Row
または、
Range("B2").CurrentRegion.Row + Range("B2").CurrentRegion.Rows.Count - 1

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


・SpecialCells(xlCellTypeLastCell)

Cells.SpecialCells(xlCellTypeLastCell).Row

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


・UsedRange

ActiveSheet.UsedRange.Item(ActiveSheet.UsedRange.Count).Row
または
ActiveSheet.UsedRange.row + ActiveSheet.UsedRange.Rows.count - 1

UsedRangeは、指定されたワークシートで使われたセル範囲を返します。
使われたセル範囲の開始行位置と使われたセル範囲の行数を足しています。
UsedRangeは、シートの指定が必須となります。



※注意してください

UsedRangeについて間違った解説を載せているサイトを見かけます。
「エクセル VBA」でググってトップの方に表示されるようなサイトでもです。

ActiveSheet.UsedRange.Rows.count

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



さらに、自力でデータが入っているかの判定をして、最終行を求める事も可能です。

最終行の判定、Rangeオブジェクトと配列、高速化の為に

参考にして下さい。




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

ユーザー定義関数の作り方
セルの値について(Value,Value2,Text)
文字列でのセル参照と文字列の計算式について(Evaluate,INDIRECT)
文字列置換の基本と応用(Replace)
空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)
空白セルを正しく判定する方法2
実行時にトラップ可能なエラー番号一覧

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

SUMIFの間違いによるパフォーマンスの低下について|エクセル関数超技(6月17日)
条件式のいろいろな書き方:TrueとFalseの判定とは|ExcelマクロVBA技術解説(6月15日)
空白セルを正しく判定する方法2|ExcelマクロVBA技術解説(5月6日)
フルパスをディレクトリ、ファイル名、拡張子に分ける|ExcelマクロVBA技術解説(4月15日)
テキストボックスの各種イベント|Excelユーザーフォーム入門(4月9日)
フォルダ(サブフォルダも全て)削除する、Optionでファイルのみ削除|ExcelマクロVBAサンプル集(4月4日)
最後の空白(や指定文字)以降の文字を取り出す|エクセル関数超技(3月26日)
先頭の数値、最後の数値を取り出す|エクセル関数超技(3月26日)
Excelファイルを開かずにシート名をチェック|ExcelマクロVBAサンプル集(3月23日)
数式の参照しているセルを取得する|ExcelマクロVBAサンプル集(3月18日)

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

1.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
2.RangeとCellsの使い方|ExcelマクロVBA入門
3.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
4.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
5.変数とデータ型(Dim)|ExcelマクロVBA入門
6.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
7.セルの参照範囲を可変にする(OFFSET,COUNTA,MATCH)|エクセル関数超技
8.ひらがな⇔カタカナの変換|エクセル基本操作
9.定数と型宣言文字(Const)|ExcelマクロVBA入門
10.CSVの読み込み方法|ExcelマクロVBAサンプル集



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

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


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

    ↑ PAGE TOP