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

ExcelマクロVBAの問題点と解決策、エクセルVBAの技術的解説
最終更新日:2019-06-17

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


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


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


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

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

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

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

以下の表で説明します。

Excel解説

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

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


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

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

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

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

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

Excel解説

この場合は、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


これらを使用します。


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なので、
未使用の行が先頭にある場合は、その行数が含まれなくなってしまうのです。



サイト内関連ページ

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

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

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

最終行の判定、Rangeオブジェクトと配列、高速化の為に
最終行の判定 エクセル顧客管理の記事からのスピンオフ記事になります。顧客管理は幅が広く、業種によっても様々です。まずは、ごく一般的なものを作成していきます。出来れば、「お客様第一」顧客管理システムに近い物ができれば良いと思います。




同じテーマ「マクロ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 ・・・新着記事一覧を見る

Withステートメントの実行速度と注意点|VBA技術解説(6月6日)
VBA+SeleniumBasicで検索順位チェッカー(改)|VBA技術解説(6月2日)
マクロでShift_JIS文字コードか判定する|VBA技術解説(6月1日)
Shift_JISのテキストファイルをUTF-8に一括変換|VBAサンプル集(5月31日)
「VBAによる解析シリーズその2 カッコ」をやってみた|エクセル(5月21日)
VBA+SeleniumBasicで検索順位チェッカー作成|VBA技術解説(5月18日)
テーブル操作のVBAコード(ListObject)|VBA入門(5月12日)
テーブル操作の概要(ListObject)|VBA入門(5月12日)
VBAのスクレイピングを簡単楽にしてくれるSelenium|VBA技術解説(5月6日)
Excelワークシート関数一覧(2010以降)|VBAリファレンス(4月22日)

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

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



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

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


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






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

    本文下部へ