Excel VBA

ワークシートの利用範囲を求める定石

　Excel VBA の定石の１つに、ワークシートの利用範囲を求める処理があります。このためには、指定したセルの下方向の最終位置を調べる Endメソッドを利用します。また同様に右端のセルを調べるのも Endメソッドが使えます。

　ここでは日本の人口推移のデータを用意しました。

End で最終行・最終列を求める方法

　指定されたセルを起点として使われている一番下の行と右端の列を調べるプログラムです。


Sub 利用範囲を調べる()
	' 範囲を調べて表示する --- (※1)
	lastLine = Range("A4").End(xlDown).Row
	lastColumn = Range("A4").End(xlToRight).Column
	
	' 結果を表示
	MsgBox _
		"一番下：" & lastLine & vbCrLf & _
		"一番右：" & lastColumn
End Sub

　このマクロを実行すると一番下の行、一番右の列を調べてメッセージボックスに表示します。

　プログラムのポイントは、(※1) の部分です。Endメソッドを利用することで求められます。ただし、この方法には欠点がありますのでご注意ください。

シートの Rows.Count は何を表している？

　ワークシートには、Rows というプロパティがあり、Rows.Count でシートの行数を得ることができるようになっています。これを使えば、最終行を得られるのでは？と期待したくなります。例えば、以下のように記述して、イミディエイトウィンドウに Rows.Count の値を出力してみます。


Debug.Print Rows.Count

　すると、古い Excelであれば 65536 を、新しい Excel であれば 1048576 を表示します。つまり、ワークシートの最大行を表しているだけです。

UsedRangeで最終セルを求める場合

　引き続き、最下行を求める方法を見てみます。最初に紹介した Endを使用した方法では、目的のシートが空だった場合に、最下行がシートの最大値（つまり、Rows.Countの値 65536 または 1048576）と判定されてしまいます。

　これに対し、使用済みの最終セルを求めるのが UsedRangeです。

　Endを使う方法と UsedRangeを使う方法の２つを比較してみます。


Sub test()
    Dim lastLine1 As Long
    Dim lastLine2 As Long
    Dim lastColumn1 As Long
    Dim lastColumn2 As Long
    
    ' endを使う場合
    lastLine1 = Range("A1").End(xlDown).Row
    lastColumn1 = Range("A1").End(xlToRight).Column
    
    ' UsedRangeを使う場合
    With UsedRange
        lastLine2 = .Item(.Count).Row
        lastColumn2 = .Columns(.Columns.Count).Column
    End With
    
    ' 結果を表示
    MsgBox _
        "End:       " & lastLine1 & ", " & lastColumn1 & vbCrLf & _
        "UsedRange: " & lastLine2 & ", " & lastColumn2
End Sub

　このプログラムを実行すると実行結果をそれぞれ End と UsedRange で調べた最終行、最終列をダイアログに表示します。

　すると、Endを利用した方法では意図しない値が表示されてしまいました。最終列の値がとんでもないことになっていますし、セルA1より続く空白部分を調べただけの値が表示されます。つまり、Endを使う方法で空白セルを指定するとおかしな値を示すのです。

　ただし、UsedRangeを利用する場合にも注意が必要です。というのは少しでも利用しているセル範囲をすべて利用済みと判定してしまうのです。罫線が引いてあったり、行の高さを少し調整しただけでも、利用済みと判定してしまいます。