ExcelマクロVBA技術解説 | 最終行・最終列の取得方法(End,CurrentRegion,SpecialCells,UsedRange) | ExcelマクロVBAの問題点と解決策、エクセル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オブジェクトと配列、高速化の為に

参考にして下さい。




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

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

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

VBAを定型文で覚えよう|ExcelマクロVBA技術解説(3月26日)
VBAスタンダード試験対策まとめ|MOS VBAエキスパート対策(3月16日)
ユーザーフォームとメニューの操作|MOS VBAエキスパート対策(3月14日)
ファイルの操作|MOS VBAエキスパート対策(3月14日)
ユーザーフォームの各種イベント|Excelユーザーフォーム(3月13日)
レジストリの操作|MOS VBAエキスパート対策(3月12日)
変数と配列|MOS VBAエキスパート対策(3月12日)
Colorプロパティの設定値一覧|VBA技術解説(3月12日)
APIとOLEオートメーション|MOS VBAエキスパート対策(3月11日)
エラーへの対処|MOS VBAエキスパート対策(3月10日)

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

1.最終行の取得(End,Rows.Count)|VBA入門
2.変数とデータ型(Dim)|ExcelマクロVBA入門
3.マクロって何?VBAって何?|ExcelマクロVBA入門
4.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
5.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
6.RangeとCellsの使い方|ExcelマクロVBA入門
7.とにかく書いて見よう(Sub,End Sub)|VBA入門
8.ひらがな⇔カタカナの変換|エクセル基本操作
9.まずはマクロVBAを使う準備|ExcelマクロVBA入門
10.繰り返し処理(For Next)|ExcelマクロVBA入門



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

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


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





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

    本文下部へ

    ↑ PAGE TOP