【第７回Excelマクロ】最終行と最終列の取得【ほぼfor/if文】
今回は、Excelの表の最終行と最終列の取得方法をまとめます。
最終行の取得
やや難しそうに見えますが、以下のコードで最終行を取得できます。まずはおまじないだと思って使ってみましょう。
Dim lastRow As Long '最終行をlong型で宣言
lastRow = Cells(Rows.Count, 1).End(xlUp).Row '最終行「.End(xlUp)」というプロパティが初めてでてきました。
Excelのセル上で Ctrlキーを押しながら「↑・↓・→・←」キーを押すと矢印方向にジャンプしますよね。このプロパティは、その操作をコードで表現したものになります。以下がその一覧です。
- .End(xlDown)→下方向にジャンプ
- .End(xlUp)→上方向にジャンプ
- .End(xlToLeft)→左方向にジャンプ
- .End(xlToRight)→右方向にジャンプ
さらに「Cells(Rows.Count, 1).End(xlUp).Row」のコードをみてみると、
- 「Cells(Rows.Count,1)」→最終行の1列目を指定
- .End(xlUp).Row→上方向にジャンプしたときの行を取得
となっており、Excelの1列目の最も下にある行（1048576行目）から「Ctrl+↑」を実行して表の最終行を取得しています。（Excel2003なら65536行目が最も下の行になっています）
また、integer型で宣言すると行数が多い表の場合にエラーになってしまうので、long型で宣言をしています。
最終列の取得
以下のコードで最終列が取得できます。
Dim lastCol As Long '最終列をlong型で宣言
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column '最終列「Cells(1,Columns.Count).End(xlToLeft).Column」のコードもみてみると、
- 「Cells(1, Columns.Count)」→最終列の1行目
- .End(xlToLeft).Column→左方向にジャンプしたときの列を取得
となっており、Excelの1行目の最も右にある列から「Ctrl+←」を実行して表の最終列を取得しています。
最終行・最終列を取得した使用例
まずは、テスト用の表を作成します。以下のコードを実行すると下の表が作成されます。
Dim ws As Worksheet 'ワークシートのオブジェクト変数を宣言
Set ws = ThisWorkbook.Sheets("Sheet1")
Dim fruitArray As Variant '果物の2次元配列
fruitArray = [{"果物","価格","個数","合計金額";"りんご",100,3,300;"なし",90,4,360;"ぶどう",200,5,1000;"すいか",2000,6,12000;"メロン",3000,2,6000;"もも",300,8,2400;"さくらんぼ",500,10,5000}]
For i = LBound(fruitArray, 1) To UBound(fruitArray, 1) 'for文で1行目に2次元配列を出力
For j = LBound(fruitArray, 2) To UBound(fruitArray, 2)
ws.Cells(i, j).Value = fruitArray(i, j)
Next j
Next i
例えば、この表の列・行数がいくつあるかわからない場合を想定します。そんなとき、表の最終行・最終列を取得することでその後の処理が非常に楽になります。
次に、取得した最終行・最終列の変数を使って2次元配列を取得してみましょう。上のコードに続けて、以下のコードを貼りつけて実行します。
Dim lastRow As Long, lastCol As Long '最終行・最終列をlong型で宣言
lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row '最終行
lastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column '最終列
Dim fruitArray2 As Variant '果物の2次元配列
fruitArray2 = Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))
For i = LBound(fruitArray2, 1) To UBound(fruitArray2, 1) 'for文で10行目に2次元配列を出力
For j = LBound(fruitArray2, 2) To UBound(fruitArray2, 2)
ws.Cells(i + 9, j).Value = fruitArray2(i, j)
Next j
Next i実行すると、テスト用の表と同じものが10行目にできます。
以下のコードのように、最終行・最終列を格納した変数を使うことで2次元配列を取得できました。
fruitArray2 = Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))最後に
最終行・最終列の取得方法と、それを使ってRangeオブジェクトの終了セルに設定することで、2次元配列も取得できました。今回の内容は今後もかなり使用していきます。また、2次元配列を取得するだけではなく、取得した最終行・最終列を使ってfor文で何回ループさせるか、といった際にも使えます。
コードの内容はやや難しいので、まずはおまじないのように使っていきましょう。