【VBA】Excelマクロで最終行(列)を取得する方法(End, Count)

VBA
スポンサーリンク

本記事では最終行(列)を自動で取得する方法を解説します。

例えばfor文の終値を自動設定するときに便利です!!

私はfor文すら知らないプログラミングのド素人からVBAとPythonを独学しました。

会社では通常業務に加えて作業自動化と機械学習をしています。

・事務作業を完全自動化したい
・for文の最終行を自動取得したい
・行や列によって処理を分けたい

こんな悩みを解決する内容です。

下図のように、データが入っている最終行と最終列を取得しましょう!

しかし、まずはセルの指定方法について知っておく必要があります。

不安な方は以下の記事を参考にどうぞ。

Countで末端の行or列を取得する

Sub test()

MsgBox Rows.Count
MsgBox Columns.Count

End Sub

本記事での末端行(列)とはExcelシート上でとり得る最大の行(列)のことを指します。

末端の行と末端の列

例えば何も入力していないシート上でCtrl + ↓をした時の行です。

今回目標としている最終行(列)とは違うので注意してくださいね。

・末端行:シート上でとり得る最大の行番号
・最終行:データが入っている最終行

Endでワープする

Sub test()

MsgBox Cells(1, 1).End(xlDown).Row

End Sub

Endは「Ctrl+矢印」をプログラム化したものです。

つまりセルの位置をワープさせる命令。

Cells(行, 列).End(xlUp or xlDown or xlToRight or xlToLeft)
(上:xlUp, 下:xlDown, 右:xlToRight, 左:xlToLeft)
Endでセル指定を変えるコード

A1セルからCtrl + ↓なので、今回のデータでは5が表示されます。

Endでセル指定を移動させた結果

CountとEndで最終行(列)を取得する

最終行:Rows.Count+.End(xlUp)
最終列:Columns.Count+.End(xlToLeft)

最終行の取得

Sub test()

MsgBox Cells(Rows.Count, 1).End(xlUp).Row

End Sub
CellsとEndで最終行を取得するコード

Cells(Rows.Count, 1)でA列末端のセルを指定しましょう。

次にEnd(xlUp)で「Ctrl + ↑」を行うと最終行に到達します。

末端業からEndで移動した結果

つまり一番下からCtrl + ↑してるってこと!!

最終列の取得

Sub test()

MsgBox Cells(1, Columns.Count).End(xlToLeft).Column

End Sub

最終列はColumns.CountからCtrl + ←でOKです。

末端列からEndで移動した結果

末端から取得する理由

先頭(A1)から.Endを使っても同じじゃない?

と疑問に思う方もいるかもしれません。

結果は同じですが、データが欠損している場合は話が変わります。

先頭からEndと末端からEndの違い

このようにC1が欠損しているデータで試してみましょう。

Sub test()

MsgBox Cells(1, 1).End(xlToRight).Column
MsgBox Cells(1, Columns.Count).End(xlToLeft).Column

End Sub

3行目のコードではA1から右へワープさせています。

4行目のコードでは末端列から左へワープさせています。

これを実行してみると、最初は2が、次に5が表示されるはずです。

絶対に全データ埋まっているならA1からの移動でいいと思いますよ。

最終行(列)を利用したプログラム

for文の終値に利用する

使用したいデータの例

このようにA列に入っているデータ全てをfor文で処理しましょう。

Sub test()

endRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To endRow
    Cells(i, 2) = Cells(i, 1) ^ 2
Next i

End Sub

A列にデータが入っている最終行をCount,Endで取得しました。

最終行の取得と計算を組み合わせたプログラムの結果

これならどれだけデータが入っていても処理できますね。

直接Rows.Countを終値にしてもよくない??

Sub test()

For i = 1 To Rows.Count
    Cells(i, 2) = Cells(i, 1) ^ 2
Next i

End Sub

この書き方はおススメしません。

まず処理時間が異常に長いです。Excelが落ちたか心配になるくらい。

加えて、数値が入っていないA列のデータも処理されるので関係ないデータが出力されます。

無難に下からCtrl + ↑をしてください。

コピー範囲を自動取得する

下図のようにA列の2~X行までをコピーしたいとします。

項目名以外をコピーしたい例
Sub test()

endRow = Workbooks("Book1.xlsx").Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row
Workbooks("Book1.xlsx").Worksheets(1).Activate
Range(Cells(2, 1), Cells(endRow, 1)).Copy
ThisWorkbook.Worksheets(1).Cells(2, 1).PasteSpecial
Application.CutCopyMode = False

End Sub

endRowは”Book1″のA列でデータが入っている最終行ですね。

コピーする範囲はA2からなのでCells(2,1)~Cells(endRow,1)としましょう。

実行するとA2から下のデータがコピーされます。

これ何の意味があるの??

例えば1行目の項目名を変えたくないときやね。

列ごとコピペするとフォーマットが崩れるから。

コピペの方法は以下の記事で解説しています。

まとめ

今回は最終行(列)の取得方法を解説しました。

実務でプログラムを組むときに頻繁に使うのでぜひマスターしてくださいね。

VBA
スポンサーリンク
HTOMblog

コメント

タイトルとURLをコピーしました