スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。
【VBA】データの入っている最終行・列を取得する

エクセルのシートに記入されているデータの最終行・列(セル番地)を取得する方法について。

やり方はいくつかあります。

①A1セルから下方向に進んでいき、空白セルがくるまで探索、
 A1セルから右方向に進んでいき、空白セルがくるまで探索する方法。


4


以下のコードで実現可能。

Sub test1()

Dim MaxRow As Long
Dim MaxCol As Long

MaxRow = Range("A1").End(xlDown).Row
MaxCol = Range("A1").End(xlToRight).Column

Debug.Print (MaxRow)
Debug.Print (MaxCol)

End Sub


Debug.Printの出力結果は、
MaxRow:10
MaxCol:5です。


いきなりですが、
※この方法には、不備があります!!!!!
そうです。以下の図のように、データの途中に空白が存在した場合、
セルの探索は、空白行で停止してしまうため、結果は上と変わらず、E10が一番右下のセルと判定されます。

3


これについては、よくあるやり方で回避できます。
そのやり方とは、

②セルの探索を下から上方向に向かって行う方法です。

コードは以下です。

Sub test2()

Dim MaxRow As Long
Dim MaxCol As Long

MaxRow = Range("A65536").End(xlUp).Row
MaxCol = Range("IV1").End(xlToLeft).Column

Debug.Print (MaxRow)
Debug.Print (MaxCol)

End Sub


Debug.Printの出力結果は、
MaxRow:22
MaxCol:5です。

最終行・列のセル位置を正しく判定できているようです。

※この方法にも不備があります!!!!!
最終行判定にA列を使用しているため、あくまでA列に入っているデータの最終行でしかない。
そのほかにも、エクセルのバージョンによってはシートの最大行数が異なる場合があるため、うまく機能しないことがあります。

そこで使用するのが以下の方法。
③SpecialCellsメソッド

A1セルを起点として、最終セル位置を探索します。

5


コードは以下のコードです。
Sub test3()

Dim MaxRow As Long
Dim MaxCol As Long

MaxRow = Range("A1").SpecialCells(xlLastCell).Row
MaxCol = Range("A1").SpecialCells(xlLastCell).Column

Debug.Print (MaxRow)
Debug.Print (MaxCol)

End Sub


Debug.Printの出力結果は、
MaxRow:22
MaxCol:5です。

※この方法にも、やはり不備があります。

一度入力されたデータを一部削除してから③のSpecialCellsメソッドで、最終行・列のセル位置を取得してみると・・・

6


結果は、
MaxRow:22
MaxCol:5です。

ここで取得したいセルの位置はE10なので、
MaxRow:10
MaxCol:5となってほしいところですが、そうはいかないようです。

このような事象が発生する原因としては、
一言で云うならば、エクセルの仕様っぽいです。

SpecialCellsメソッドで対象としている探索範囲は、
実際に見えている(今ある)データ範囲ではなく、
使用済みセルの範囲を探索しているために、今回のような結果となっているようです。
※詳細は・・・
【VBA】範囲選択のワナ



SpecialCellsメソッドではなく、UsedRangeプロパティを使用する方法もあります。

④UsedRangeプロパティを使用する方法。

その名の通り、使用しているセル範囲を返します。

コードは以下の通りです。

Sub test4()

Dim MaxRow As Long
Dim MaxCol As Long

MaxRow = ActiveSheet.UsedRange.Rows.Count
MaxCol = ActiveSheet.UsedRange.Columns.Count

Debug.Print (MaxRow)
Debug.Print (MaxCol)

End Sub


結果は、
MaxRow:22
MaxCol:5です。

途中に空白行が入り込んでいても、うまく判定できています。
しかし、これも一度使用したセルの範囲をとってくるっぽいので、③と同じような現象が発生します。

さらに・・・
上記のコードでは、あくまでカウントを取っているため
セルA1からデータが始まっていなければ、結果は期待した結果(データが入っている一番右下のセル位置情報)は得ることができないでしょう。

例としては、以下の図のような、1列目1行目にスペースが入っている場合です。

7


この場合、test4の実行結果は、
MaxRow:10
MaxCol:5です。

あくまで使用されているセルのカウントなので、使用していない1行目1列目は無視されているようなイメージです。

そこで、どうするかというと、これも②と同じような方法で回避できます。
⑤UsedRangeで最下端、最右端から探索を行う方法。

以下のコードで実現可能です。

Sub test5()

Dim MaxRow As Long
Dim MaxCol As Long

MaxRow = ActiveSheet.UsedRange.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
MaxCol = ActiveSheet.UsedRange.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column

Debug.Print (MaxRow)
Debug.Print (MaxCol)

End Sub


結果は、
MaxRow:11
MaxCol:6です。
正しく右下のセル位置情報が取得できました。

まとめ
使い分けが肝心。
スポンサーサイト
上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。