なにぬねのーつ

No.8 ワークシートの最終行、最終列を取得する
2007/03/05 VBA
ワークシートの最終セル（最終行、最終列）を取得する…
VBAをはじめた人は誰しも最初に引っかかる問題ではありますが、幸いにもインターネットで検索するといくつもの解決法が見つかります。
ところが…動いたり動かなかったり、想定外の動作をすることがあります。そもそもいくつもの解決法があること自体おかしい、いったい本当の答えはどれ？この際、白黒はっきりさせようじゃないかというのがこのページの趣旨です。

<S1> ワークシートの最大行、最大列を取得する

MaxRow = Rows.Count
MaxCol = Columns.Count
まず基本からですが、ワークシートの最大行、最大列を取得するコードです。もちろんこれ自体は最終行、最終列を取得するものではありません。
Rowsはワークシートの行全体をあらわすオブジェクトで、Columnsは列全体をあらわすオブジェクトです。そのメソッドCountは数、すなわちそれぞれ行数、列数をあらわします。
返される値はEXCELのバージョンによって異なりますが、EXCEL97/2000/2003では65,536行、256列になります。先ほど発売されたEXCEL2007では1,048,576行、16,384列で、セル数にして17,179,869,184（171億）、従来の1024倍という膨大な量になります。65億全人類の名前が入ることになるわけですが…。

<S2> 下方向に最終行を検索する

MaxRow = Range("A1").End(xlDown).Row
MaxCol = Range("A1").End(xlToRight).Column
次はRangeオブジェクトのEndプロパティを使ってみましょう。End(xlDown)とは下方向に現在の領域の最下端を探すという意味です。マクロで記録するとわかりますが、このEnd(xlDown)はショートカットの[Ctrl]+↓にあたります。このコードは連続したセルである場合、正しく最終行を取得することができます。

以下のようなシートを用意してみましょう。A1にカーソルを置き、[Ctrl]+↓を押してみます。期待通りその列の最終行であるA10にカーソルが移動することがわかると思います。

ところがシートの途中で抜けのセルがある場合、以下のような動作をします。値の存在する連続したセルを一つのブロックとして、その上端、下端を移動します。言い換えると空白の変わり目を探しているといってもいいかもしれません。

結論として、このコードはすべてのデータが連続したセルであることが保証されている場合には使用できますが、そうでない場合は使えないということです。


<S3> 上方向に最終行を検索する

MaxRow = Range("A65536").End(xlUp).Row
MaxCol = Range("IV1").End(xlToLeft).Column ' IV1=256列
これはもっとも知られている方法でしょうか。VBAの参考書などでも例としてでているようです。
<S2>ではセルが連続していない場合は×でしたが、こちらは連続していなくてもちゃんと動作します。仕組みとしてはワークシートの最下端（65536行）から上に向かって空白でないセルを検索するというものです。これだと途中で空白セルがあっても問題ないですよね。

ただし、このコードには問題が3つあります。

EXCELのバージョンによって最大行数が異なるため、EXCEL97〜2003では動作するが他のバージョンでは動作しない場合があります。ただし当面EXCEL2007も使わないし使ったとしても取り扱うデータ量がそう多くないという場合は問題にはならないかもしれません。
A列だけ見ているので、他の列にA列の最大行を超える行が存在した場合正しい値を取得できません。
65536行すべて埋まっていた場合は動作しないでしょう。（そうあることではありませんが）

<S3-1> 上方向に最終行を検索する（改）

MaxRow = Cells(Rows.Count, 1).End(xlUp).Row
MaxCol = Cells(1, Columns.Count).End(xlToLeft).Column
これは の問題を解消するものでEXCELのバージョンにかかわらずちゃんと動くようになります。数値座標を引数とするためCellsを使用していますが結果はRangeを使用した場合と同じ結果になります。


<S4> 使用済みの最終セルの選択 （SpecialCells）

With Range("A1").SpecialCells(xlLastCell)
    MaxRow = .Row
    MaxCol = .Column
End With
次にSpecialCellsメソッドを使用した例です。SpecialCellsの引数としてxlLastCellを指定すると"使用済みの最終セル"という意味になります。これはショートカットの[Ctrl]+[End]（使用済みの最終セルを選択）や[Ctrl]+[Shift]+[End]（使用済みの最終セルまで領域を拡張）に相当します。

やはりこの方式にも欠点があります。

行や列を削除した後、その結果を反映しないことです。つまり削除前の結果を返すということです。

上のようなシートがあり、4〜6行を削除します。
A1にカーソルを置き、キーボードから[Ctrl]+[Shift]+[End]を押します。

すると、削除前の領域である"A1:E10"が選択されてしまいます。これはEXCELの仕様かバグなのかは不明ですが、結果として正しく判定できていないことは確かです。ちなみに一旦ワークブックを保存すると正しく判定できるようになります。
また行の挿入やセルのコピーをした場合は直後でも正しい結果が返るから不思議なものです。

もう一つの問題は値が入っていないセルまで認識してしまうことです。書式（背景色や罫線）が設定されている場合、その領域も使用済みセルとして認識してしまいます。以下の例では"E11"を最終セルとして認識してしまいます。

<S5> 使用済みの最終セルの選択 （UsedRange）

With ActiveSheet.UsedRange
    MaxRow = .Rows.Count
    MaxCol = .Columns.Count
End With
いよいよ本命の登場です。UsedRangeプロパティは指定されたワークシートで使われたセル範囲を返します。
ですがこのコードには明らかな間違いがあるのです。
以下のようなワークシート（最初の行、列に値がない）の場合、UsedRangeは"B2:E10"（黄色の枠線で囲まれた領域）になります。Rows.Countはその領域の行数なので9、同じくColumns.Countも9となり、9列9行つまり"D9"を返してしまいます。

また、UsedRangeの特性として非表示の行・列やオートフィルタで非表示になっている領域も含みますので注意が必要です。

<S5-1> 使用済みの最終セルの選択（改） （UsedRange）

With ActiveSheet.UsedRange
    MaxRow = .Rows(.Rows.Count).Row
    MaxCol = .Columns(.Columns.Count).Column
End With
<S5>の誤りを訂正したバージョンです。ややこしい表現ですが展開していくとわかりやすいです。
.Rows(.Rows.Count).RowはUsedRange.Rows(9)つまり"B2:E10"の9行目を表し、さらにその.Rowということはワークシートの10行目ということになります。

ただし、このコードも<S4>同様書式付き空白セルまでカウントしてしまう問題は残っています。

<S6> 書式付きセルを除外する （UsedRange）

With ActiveSheet.UsedRange
    MaxRow = .Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
    MaxCol = .Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
End With
書式付きセルを除外して値の入っている範囲内の最終行、最終列を求める方法です。UsedRangeの最下端、最右端からそれぞれ値の入っている最終行、最終列を検索するというもののです。

Rangeのメソッドであるfindは検索ダイアログ（メニューから[編集]-[検索]）そのものです。引数もダイアログの各オプションとほぼ一致します。第1引数は検索文字列、ここでは"*"つまり空でないなんらかの値が入っていればマッチします。第3引数は検索対象でxlFormulasは数式、xlValuesで値を表します。第5引数は検索方向、xlByColumnsで列方向、xlByRowsで行方向に検索します。第6引数は唯一ダイアログにはなくxlNextで前方に検索、xlPreviousで後方に検索というわけです。

まとめ

最後のコード<S6>はこれといった欠点もなく一応模範解答といえるものですが、正解かというとそうでもなく、VBAプログラムには相性というものがありそれぞれ答えが違うのではないでしょうか。たいていのプログラムは<S3>あるいは<S3-1>で問題なく動作するのです。
また、行・列の非表示やオートフィルタの有無によってそれぞれ動作が異なります。それぞれの癖を熟知して使い分ければいいのではないかというのが結論とさせていただきます。

こちらのページに一覧表でまとめましたのでどうぞ。
関連情報：

使用条件・免責事項



[戻る]