きなこSHOW

どうやったら寝たまま仕事ができるかあれこれ考える。

Excel VBAで最大列を取得する関数を作ってみました

お次は値が入力されている最終列、最大列といいますか、それを取得する関数を公開します。
前回うpした最終行関数は呼び出し例もなく不親切だったので、今日はそんな事も書いてみようかなと思ってます。

まずはサンプルコードをご覧ください。

データが入力されている最終列を取得するサンプル

データが入力されている最終列を取得する関数、サンプルコードの使用はすべて自己責任でお願いいたしますぜ。

呼び出し例

それでは前回紹介した最終行と、今回の最終列取得関数の呼び出し方を説明します

クドい...

たかだか最終行や最終列の取得なのにクドいコードと仰るなかれ。
クドいのには相応の理由があります。

まずこんな表があったとして、いくつかのケースで最終行を取得したいとします。
例1

この表の最終行をUsedRangeを使って取得すると、15が返ります。
でも、UsedRangeなんかで取得できるほどウチの業務は単純じゃねんだよって方、多いと思います。
Excelの表はデータとしてだけでなく、印刷物を配布したりイントラに掲載したりして見るためにデータ以外の文字が入ってる場合もあったり、利用されるシーンはさまざまでしょう。
なんでもIT部門が主役というわけではないですよね。

データは増えたり減ったりして行数は毎回定まらないけど、合計の数式が入ってる行までは要らなくて、商品名が入ってる列の中で最大行が欲しいとき、この例では今のところ14行目となっている行(伝わります?)を取得するには、
基準となるKEY列を指定して「F_GetRowMax」を呼び出します。
商品名が入っている列=3列目を基準として最終行を求めるには、次のように指定します。

If F_GetRowMax(objWS, 3, lngRowMax, strMsg) = False Then
    …

F_GetRowMax関数では、最終行を検出したら結果を第3引数に格納して返します。

同様に最終列を取得する場合も、基準となる行が決まっていればその行を指定して「F_GetColMax」を呼びます。
例えば1行目に見出しがあるとしてその最終列を取得する場合は以下のように、基準行に1を指定します。

If F_GetColMax(objWS, 1, lngColMax, strMsg) = False Then
    …

F_GetColMax関数では、最終列を検出したら結果を第3引数に格納して返します。
上の表なら、4が返ります。

一方、値が必ず入っている行/列がない場合、どの行もどの列も基準にできないので、次のように基準行/基準列の指定を省略して関数を呼び出します。

If F_GetRowMax(objWS, , lngRowMax, strMsg) = False Then
    …
If F_GetColMax(objWS, , lngColMax, strMsg) = False Then
    …

この場合返ってくるのはUsedRangeの最終行と最終列です。

使ってはいけないプロパティ・メソッド

多くのサイトで最終行・最終列の取得方法が紹介されていますが、逆にこれは使っちゃだめっていうやつを紹介します。

  • .End(xlDown).Row
    よく取り上げられている「Sheet.Range("A1").End(xlDown).Row」
    これでは、A列で最終行までの間にどこか空白が含まれていたら、正しい最終行が取れませんよね。
    調べる列もA列じゃなくて可変にしたい。
  • .SpecialCells(xlLastCell).Row
    それから「.Cells.SpecialCells(xlLastCell).Row」を使っているパターンもいまだに見かけますが、これやめたほうがいいス。
    この「SpecialCells」は保護されたシートでは使えません。
    筆者もかつては使ってましたが、利用部門から指摘されて使うのやめました。
    同様に「CurrentRegion」も保護されたシートでは使用できません。
    普通にシート保護することもあるでしょう。ユーザーは悪くないです。
    この2つはね、ユーザーさんから叱られちゃうから使うのやめましょうね、ホントにね(涙)

UsedRangeは値だけでなく書式設定も含まれてしまうけど大丈夫

ここまで言っておいてアレですが、今回使用したUsedRangeプロパティにも弱点があります。
UsedRangeプロパティの性質なのですが、セルに背景色やら罫線やらコメントやらが設定されているとUsedRangeに含まれてしまうのです。
例えば下図のようなシートでは、UsedRangeの最終セルはC8になってしまいます。

例2

「F_GetRowMax」「F_GetColMax」ではこの対策として、基準列/行が指定されなかった時は、UsedRange最終セルからさかのぼって値が入力されているセルを探して、見つかった行/列を最終と判断するようにしました。

すべてのパターンではないですけど、最終行・列の取得はこの関数でだいたいカバーできそうです。

いやー、なんだかプログラミングヲタ心に火が付いちゃって、長い文章になってしまいました。
最終行・最終列の取得は、利用するシーンに合った最適な方法をご自身で探しだしてくださいね!