エクセルで最終行または最終列を知りたいことはないでしょうか。
今回はマクロVBAで最終列または行番号を取得する方法を紹介します。
たとえばFor Toで繰り返す回数を決まっていれば問題はありませんが、
データ数が不明な場合は困ってしまいますよね。
データ数だけ繰り返したいときは最終行・列を知る必要があります。
さらに最終行・列にデータを追加していくシステムには必須の機能です。
今回はそんな最終行・列をマクロVBAで求める方法についてまとめました。
それでは見ていきましょう。
■プログラムの流れ
最終行の反映先 = 開始位置.End(データ型).Row
最終列の反映先 = 開始位置.End(データ型).Column
| データ型 | 最終行・列を求める方向 |
|---|---|
| xlDown | 下 |
| xlUp | 上 |
| xlToRight | 右 |
| xlToLeft | 左 |
■最終行を求める
・セルの先頭から最終行を求める
では早速、最終行を求めてみましょう。
下図の見本をご覧ください。
A列に数値が並んでいます。
今回はA列の最終行を求めて「Ｃ3」に出力してみましょう。
プログラムの構造はこのようになります。
最終行の反映先 = 開始位置.End(データ型).Row
反映先は結果を表示する「Ｃ3」になります。
開始位置はデータの開始位置の「A1」ですね。
データ型は下方向に最終行を求めたいので「xlDown」となります。
それではプログラムに置き換えてみましょう。
プログラムではこのようになります。
Range(“C3”) = Range(“A1”).End(xlDown).Row
では下記のマクロVBAを走らせてみましょう。
Sub 最終行の取得()
Range(“C3”) = Range(“A1”).End(xlDown).Row
End Sub
結果はこのようになります。
最終行を求めることができました。
・最終行を求める開始位置を変更する
ではA列に隙間が空いた場合を見てみましょう。
先ほどのプログラムを実行すると結果は「14」と変わりません。
これは「A1」から開始してデータが無い行までを求めるので、一番近い「14」を算出します。
なので実際の最終行の「18」を表示したい場合は開始位置を変更することが必要です。
では開始位置を「A16」からにしてみましょう。
Sub 最終行の取得()
Range(“C3”) = Range(“A16”).End(xlDown).Row
End Sub
結果はこのようになります。
「A16」からの最終行を算出した「18」が結果として表示されました。
・変数に最終行を格納する
ちなみに最終行を求めた結果は変数にも格納が可能になります。
下の見本をご覧ください。
Sub 最終行の取得()
Dim EndRow As Variant
EndRow = Range(“A1”).End(xlDown).Row
End Sub
このプログラムでは変数名「EndRow」に最終行を格納することが可能です。
もちろんこの数値を活用してセルを選択したり、
For Toなどといったものにも活用することもできます。
■最終列を算出する
・セルの先頭から最終列を求める
今度は最終列を求めてみましょう。
下図の見本をご覧ください。
１行目が何列までデータがあるか求めていきます。
今回は最終列を求めて「B4」に出力してみましょう。
プログラムはこのようになります。
最終列の反映先 = 開始位置.End(データ型).Column
反映先は結果を表示する「B4」になります。
開始位置はデータの開始位置の「A1」ですね。
データ型は下方向に最終行を求めたいので「xlToRight」となります。
それではプログラムに置き換えてみましょう。
プログラムではこのようになります。
Range(“B4”) = Range(“A1”).End(xlToRight).Column
では下記のマクロVBAを走らせてみましょう。
Sub 最終列の取得()
Range(“B4”) = Range(“A1”).End(xlToRight).Column
End Sub
結果はこのようになります。
データは「Ｊ列」つまり「１０列目」までデータがあるので
B4には「10」が出力されました。
・最終列を求める開始位置を変更する
では１行目のデータに隙間が空いた場合を見てみましょう。
４列目が空白なので「3」が出力されました。
これは「A1」から開始してデータが無い行までを求めるので、一番近い「3」を算出します。
なので実際の最終行の「10」を表示したい場合は開始位置を変更することが必要です。
では開始位置を「E1」からにしてみましょう。
Sub 最終列の取得()
Range(“B4”) = Range(“E1”).End(xlToRight).Column
End Sub
結果はこのようになります。
「E1」から右方向に最終列を算出した「10」が結果として表示されました。
・変数に最終列を格納する
ちなみに最終列を求めた結果は最終行と同様、変数にも格納が可能になります。
下の見本をご覧ください。
Sub 最終列の取得()
Dim EndColumn As Variant
EndColumn = Range(“E1”).End(xlToRight).Column
End Sub
このプログラムで行くと変数名「EndColumn」に最終列を格納することが可能です。
もちろんこの数値を活用してセルを選択したり、
For Toなどといった繰り返し処理の変数としても活用することもできます。
■空白の位置が複数・不明な時でも最終行・列を求める。
これまで最終行・列を求める方法として
先頭から求める方法や、開始位置をずらして求める方法など説明しました。
しかし空白の位置が不明な状態では開始位置すらわからないため
空白をよけることが不可能です。
このような状態でも最終行・列は求めることができます。
例で最終行を求めます。
考え方はこれまで、先頭行を入力して下方向に最終行を求めてました。
この考え方を変えて、エクセル自体の最終行から上方向に最終行を求めます。
ちなみにエクセルの最終行を求める方法は下記の通りです。
変数 = Rows.Count
こちらを実行してみた結果はこちらです。
変数はEndRowを使用しました。
変数EndRowには「1048576」となってます。
つまり現在私が使用しているエクセルの最終行は「1048576行」となります。
そこから上方向に最終行を求めれば最終行を導き出すことが可能です。
つまりデータ型は「xlUp」を使用します。
ではプログラムです。下記のようになりました。
Sub 空白を無視した最終行()
Dim EndRow As Long
EndRow = Rows.Count
Range(“C3”) = Cells(EndRow, 1).End(xlUp).Row
End Sub
実行した結果はこのようになります。
空白をよけて、下から上方向に最終行を求めることで
正解の「20」を表示することができました。
B列なら
求めた結果= Cells(EndRow, 2).End(xlUp).Row
Ｃ列なら
求めた結果 = Cells(EndRow, 3).End(xlUp).Row
で対応可能です。
列でも同様です。
考え方は全く同じで、エクセル自体の最終列を変数に置き、
そこから左方向に最終列を求めることで空白を無視した最終列を求めることができます。
プログラムは下記の通りです。
Sub 空白を無視した最終列()
Dim EndColumn As Long
EndColumn = Columns.Count
Range(“B4”) = Cells(1, EndColumn).End(xlToLeft).Column
End Sub
こちらも２列目なら
求めた結果= Cells(2, EndColumn).End(xlToLeft).Column
3列目なら
求めた結果 = Cells(3, EndColumn).End(xlToLeft).Column
となります。
■公式の説明
わかりやすいように説明したため公式と使用する語句が異なりますが
マイクロソフト公式の説明については下記のリンクを参照してください。☟
Range.End プロパティ (Excel) | Microsoft Docs
■まとめ
いかがだったでしょうか。
最終行の求め方がわかれば
データの蓄積を最終行・列で追加していくことも可能です。
さらにどこにあるのかわからない空白を無視できる方法は
とても便利な機能です。
それでは次回の記事でお会いしましょう。
