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)
実行時にトラップ可能なエラー番号一覧
論理積(And)論理和(Or)と真(True)偽(False)の判定

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

メモの挿入・削除と改行文字|Google Apps Script入門(12月6日)
リンクの挿入・編集・削除|Google Apps Script入門(12月6日)
セルに数式を入れる|Google Apps Script入門(12月1日)
セルのコピー&各種ペースト|Google Apps Script入門(11月22日)
Twitter Bot 作成|Google Apps Script応用(11月6日)
Excel流の最終行の取得|Google Apps Script応用(11月6日)
方眼紙Excelが楽に入力できるVBA|ExcelマクロVBAサンプル集(11月5日)
「ポケモンを確実に見つける方法」をExcelで数学してみた|エクセル雑感(11月4日)
スプレッドシート(ブック)の作成・名前変更|Google Apps Script入門(11月4日)
シートの挿入・削除・名前変更|Google Apps Script入門(11月3日)

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

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



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

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


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



    ↑ PAGE TOP