Excelのバージョンに依存しない最終セルの取得方法

質問

VBAでたとえばA列の最後にデータが入っているセルを取得するのに、今まで
Range("A65536").End(xlUp)
という書き方をしてきました。

しかし、Office2007ではExcelの最大列数が従来の256列から16384列に，最大行数が従来の65536行から1048576行に増えました。

そうすると、Excelを2007にバージョンアップしたら、上記の書き方をした既存のマクロを使っているブックのデータが増えていって65536行を超えたとき、マクロが正常に稼動しなくなります。

Range("A1048576").End(xlUp)
とすればExcel2007では動くのかもしれませんが、Excel2003以前のバージョンでは、A1048576などというセルはないのでエラーになります。

できるだけExcelのバージョンに依存しない書き方をしたいのですが、上記のようなＡ列の最後にデータが入っているセルを求めるには、どういう書き方をすればいいのでしょうか。

自分なりに考えたのは、行数だけ求めるなら、
Range("A1").CurrentRegion.SpecialCells(xlCellTypeLastCell).Row
で、この値をInteger型の変数でFor～Nextでループさせる、という方法です。

しかし、ネットで検索していろいろ調べたところ、上記の書き方では不具合が生じることがわかりました。たとえばA1からA10まで値が入っていた場合、
Range("A1").CurrentRegion.SpecialCells(xlCellTypeLastCell).Row
では10が求まりますが、A10のセルを選択してDelキー（またはBackSpace）で値を削除しても、上の式の結果は10のままなのです。（Selectすると、空白のセルが選択されます）

Excelのバージョンに依存しない、データが入っている最終行の求め方でいい方法はないでしょうか？

回答No.7

Wendy02 · ベストアンサー

こんにちは。

私自身は、あまり、Excel 2007 VBA自体に触ることもないような気がしているので、特に、あまり気にしていないのですが、こんな風にすればよいですね。

以前は、こう書きましたが、明示的ではないので、廃れたのだと思います。また、復活ですね。

　Cells(Rows.Count, 1).End(xlUp).Select

>Range("A1").CurrentRegion.SpecialCells(xlCellTypeLastCell).Row

それ自体に、無理がありますね。CurrentRegion と、SpecialCells(xlCellTypeLastCell) との結びつきが薄いからです。親戚ではあっても、根っこの違うものを、繋げて使うのは良くありません。SpecialCells(xlCellTypeLastCell) これ自体が、勝手に範囲を取ろうとするからです。

SpecialCells は、ワークシート系（オブジェクト・ブラウザの[Excel]という項目に入ります）で、一般的にVBAでは用いられますが、SpecialCells メソッドは、UsedRangeでも、CurrentRegion でもない、独自の範囲の取り方をします。おそらく、ワークシートのショートカットなどとつながっているものだと思います。もちろん、ないはずのA10 の位置は、一旦保存すれば直りますが。

CurrentRegion で、取るというのでしたら、以下のようになりますね。良かったら試してみてください。

With ActiveSheet.Range("A1").CurrentRegion
　i= .Cells(.Count).Row
End With

なお、Version で行数などを替える方法には、#if Directive 構文という方法がありますが、そこまでの致命的な問題はなさそうです。

お礼

昔はRow.Countを使っていたのですね。SpecialCellsに関しても詳しい解説をありがとうございます。Wendy02さんのご回答はいつも勉強になります。どうもありがとうございました。

回答No.8

driverII

こんなのがありました。

ActiveSheet だとして、

Cells(ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row(), 1).Select

参考URL：

http://www.microsoft.com/japan/technet/scriptcenter/resources/qanda/feb06/hey0215.mspx

お礼

Rows.Countを使うことで解決しました。
ご回答ありがとうございました。

回答No.6

gatt_mk

Dim myLastCell As Range
Dim r As Integer

　　Set myLastCell = Range("A1").SpecialCells(xlCellTypeLastCell)
　　If myLastCell.Value = "" Then
　　　　r = Cells.Find("*",myLastCell, , ,xlByRows,xlPrevious).Row
　　Else
　　　　r = myLastCell.Row
　　End If

　　Msgbox "最終行は" & r & "行です"

上記VBAスクリプトを実行したらどうなりますか?

よくあるパターンです。ExcelVBAの参考書を1冊買われたらいかがですか?

お礼

VBAの参考書は持っていて、それで勉強してはいるのですが、この回答のパターンは載っていませんでした。
（「最終行の求め方」で、質問文に書いたRange("A65536").End(xlUp)が載っていたのですが…）
Rows.Countを使うことで解決しそうです。ご回答ありがとうございました。

回答No.5

driverII

１． Application.Versionでバージョンを調べ、グローバル変数に
対応した最終行を入れておく。

２． Range("A65536").End(xlUp)と同様にCells(最終行,1).End(xlUp)

じゃ駄目ですか？

お礼

Rows.Countを使うことで解決しました。
Application.Versionでわけるという方法も、他に異なる仕様が出てきた場合に使えそうですね。勉強になりました。
ご回答ありがとうございました。

回答No.4

noname#181803

ちょっと、回答とは違うんだけど・・・。
基本的に、ソフトウェア開発というものは下位バージョンとの互換性は考慮するけど、上位バージョンとの互換性は考慮できない。
そのため、どんなアプリでも対応OSを記載してる。
基本的な考え方としては、バージョンを取得して処理を分岐させるのがもっとも一般的なものだと思う。
今回の場合なんかは、Application.Version関数を利用してバージョンごとに異なる数値を割り当てるのがメンテナンスしやすいんじゃないかな～？
Selectステートメントにしておけば、Caseを追加してくだけで今後行の最終値については取得できるようになると思うし。

あと、将来的に、対応できるようなプログラムというものは作ることができない。
それは、現在のプログラムというものはOSの上でのみ動くものだからね。
OSが進化している以上、プログラムの対応は必然的に発生するし・・・。

・・・とこんなこといってもしょうがないので、ちょっと考えてみました。（相当強引）
1行目から＋1行していく無限ループ作って、エラーをわざと発生させる。
Excel2007は1048577でエラーが発生するし、Excel2007以外では65537でエラーが発生する・・・はず。
そのエラーをOn Errorステートメントでキャッチして、その値から-1すれば求められるんじゃないかな？
そのあと、Range(変数).End(xlUp)で求められると思うけど・・。
テストしたわけじゃなく、しかも、あくまで私の頭の中なんで参考程度にね・・・。

ただ、ロジック的に綺麗じゃないし、処理は遅くなるだろうし、相当邪道っぽいけどね。(^.^;;)

お礼

私もソフトウェア開発に携わっていたので、仰ることはよくわかります。確かに上位バージョンとの互換性というのは、将来のバージョンで仕様がどう変わるかわからないので、考慮しようがないですね。

しかし今回の質問に関しては、Excel2007というのがまだ誰も仕様がわからない、という未知のものでなく既に発売されており仕様もわかっており、
「Excel2007で動作するが、まだ2003や2000を使っている人が多いことを考慮にいれて、それらでも支障なく動くようなVBAの書き方は？」
ということなので、どちらかと言うと下位互換性の話になるのかな～という感じです。

Application.Versionでわけるという方法は、他に今までのバージョンと違う仕様が出てきた場合（関数の仕様などが微妙に変わったりだとか。あるかな～、ＭＳならこっそりやりそう…）に使えそうですね。参考になりました。

ご回答どうもありがとうございました。

回答No.3

hana-hana3

ベストな方法は解りませんが、単純な置き換えなら・・・
MsgBox Range("A" & Range("A:A").Count).End(xlUp).Row

お礼

この方法でもできますね。いろいろ勉強になります。
ご回答ありがとうございました。

回答No.2

uro_tan

ActiveSheet.Rows.Count
ではどうでしょうか。

お礼

これが一番シンプルかつ確実そうですね。
もっとよく考えたら自分でも気づいたかもしれませんが、まだまだ勉強が足りないみたいです。ご回答ありがとうございました。

回答No.1

nobu555

入力済みセルと空白セルの合計で総数を求めては？

お礼

Rows.Countを使うことで解決しました。
ご回答ありがとうございました。