エクセルで最終行を求めるVBAの記事が意外にもよく読まれている。
この記事でも書いたように、僕は基本的にEND(xlDown)という記述を使わないようにしてきたが、記事に書いたようなエクセルの不具合にもこのところお目にかかっていないし、そろそろもういいんじゃないかと思い出してきている。
それとは別に、エクセルで表を作っていると「データがない場合、セルは空白のまま置いておく」という作り方をすることが少なくない。VBAを使って処理をする場合、空白セルというのは何かと邪魔になるのでできれば「なし」とでも入れておいて欲しいのだが、人間的感覚からいうと空欄にしておくのが「データなし」ということがパッとみてわかる視認性抜群の方法なのだ。
そういった「データなし」の空欄がある虫食い状態になった列の、先頭行から最終行までをVBAで取得するのに頭を悩ませる人も多いのではないかと思う。
END(xlDown)で虫食い列の最後に辿り着くのは難しい
特定の列の先頭行から最終行までを選択するには
Range(Range("A1"), Range("A1").End(xlDown)).Selectとする。
この構文は
Range(先頭のセル, 最後のセル)という書式に従っている。上記の例ならば「先頭のセル」は Range(“A1”) で、カンマに続き「最後のセル」を Range(“A1”).End(xlDown) とし、A1のセルから下方向に最後に入力されているセルを求めている。
「最後に入力されている」とは「そのセルの下のセルが空白である」ということなので、この構文では空白を含む虫食い列は本当の最終行までを選択することはできない。
もちろん虫食いなくデータが詰まっている場合には正しく先頭から最後までを選択する。
END(xlDown) でダメなら END(xlUp)
xlUpで最終行？は？と思われるかもしれないが、これがそうでもない。きちんと最終行を求めることができる。しかも虫食いでも。いや、虫食いだからこそ。
Range(Range("A1"), Cells(Rows.Count, 1).End(xlUp)).Selectとする。
先ほどの書式に当てはめて考えると、「先頭のセル」は END(xlDown) を使った時と変わらず、「最後のセル」が Cells(Rows.Count, 1).End(xlUp) となっている。
Rows.Count とはすべての行数である。ここでシートの最終行つまり、Rows.Count は1048576行である。そこから End(xlUp) で上方向に「最後」を求める。ここでいう「最後」とは、1048576行目から上方向に延々と続いている「空白の最後」である。
ただし…
空白を含む列の先頭から最後の行までは取得できるが、この列が空白を含む可能性がある以上、列の最後の行ではあっても表の最終行とは限らない。
左のような表を考えてみた時、B列にデータのある最終行は12行目だが、表の最終行は15行目である。
なので空白を含む列の先頭から表の最終行までを選択あるいは取得したい場合は、セルのデータが必ず入力される列（先頭から最後までデータが詰まっている列：左の例の場合はA列）で最終行を特定する必要がある。
コメント