7.2 データがある最終行を知る方法
データが何行まで入っているか分からないワークシートの最終行がどこかを知りたいことはありませんか?まさか、行の先頭からカラのセルが見つかるまで1つずつ検索するなんて面倒なこともしたくないでしょう。

そういえば、エクセルで[CTRL]+[↓]を押すとデータの最終行へカーソルがジャンプしますよね?これと同じ操作を、マクロでは???.End(引数).操作を使えばできるんです。もちろん、カーソルを飛ばさずに、行番号や列番号だけを知ることも可能です。また、UsedRangeプロパティを使うと、さらに確実&簡単にデータの範囲を調べることが出来ます。

ちなみに、???の部分は、お馴染みのRangeCellsSelectionなどのセルを指定するオブジェクトです。詳しくは、5.1 セルや行列を扱うための基本をどうぞ。
Endプロパティを使ってデータがある最終行を知る方法
まずは、データがある最終行の行番号を取得する場合です。???.End(xlDown).Rowを使います。例では、A列の5行目から下方向xlDown)へ検索し最終行を探しています。もしも、上方向へ探すならxlUpを指定します。

ところで、行の検索なのに???の部分にはRows(1)ではなく、Range("A1")を使っていますが、これは検索を開始する位置を問わないようにするためです。例えば、Range("A100")とすると、100行目以降を検索するようになります。データが必ず1行目から入っているとも限らないでしょうし(笑)

なお、コツは必ずデータが入っている(←間違いではありません)を指定することです。テーブルから取得したデータであれば、プライマリキーのどれかを使えば、まず間違い無いでしょう。

ただし、指定したセル以降の行にデータが全くないときは、ワークシートの一番底の行番号(65,536行や1048,576行)が返ってくるのでご注意を…。ちなみに、カーソルを飛ばす(セルを選択する)ときは、Range("A1").End(xlDown).Selectと記述します。

リスト 7-2-1Endプロパティを使って最終行を求めるプログラム
1
2
3
4
5
6
7
8

Module1
Option Explicit

Sub EndDownSample()

    'A1セルを基準にして最終行を求めます
    MsgBox "最終行は : " & ActiveSheet.Range("A1").End(xlDown).Row

End Sub
Endプロパティを使ってデータがある最終列を知る方法
今度は、データがある最終列の列番号を取得する場合です。???.End(xlToRight).Columnを使います。例では、A列の5行目から右方向xlToRight)へ検索し最終列を探しています。もしも、左方向へ検索するならxlToLeftを指定します。他、基本的には行を検索する場合と同じです。

コツは必ずデータが入っている(←間違いではありません)を指定することです。見出し行などがある場合は、それを使えばまず間違い無いでしょう。

ただし、指定したセル以降の列にデータが全くないときは、ワークシートの右端の列番号(256列や16,384列)が返ってくるのでご注意を…。ちなみに、カーソルを飛ばす(セルを選択する)ときは、Range("A1").End(xlToRight).Selectと記述します。

リスト 7-2-2Endプロパティを使って最終列を求めるプログラム
1
2
3
4
5
6
7
8

Module1
Option Explicit

Sub EndToRightSample()

    'A1セルを基準にして最終列を求めます
    MsgBox "最終列は : " & ActiveSheet.Range("A1").End(xlToRight).Column

End Sub
UsedRangeプロパティを使う方法
最後は、UsedRangeプロパティを使う方法です。このプロパティは、データが埋め込まれているセルの範囲を表すプロパティです。例えば、下の図のようなワークシートであればRange("B2:D4")と同じ結果が返ります。

ちなみに、UsedRangeはRangeオブジェクトを返すため、SelectメソッドなどRangeオブジェクトのメソッドが使えます。

図 7-2-1データがまばらな状態

Endプロパティとの決定的な違いは、途中にデータが入っていないセルがあるときです。Endプロパティでは、データに抜けがあるとデータの底を正しく得ることができませんが、UsedRangeを使うとほぼ*1確実に底を調べることができます。

リスト 7-2-3UsedRangeプロパティを使って最終列などを求めるプログラム
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

Module1
Option Explicit

Sub UsedRangeSample()

    Dim oRange   As Range
    Dim lColumnL As Long
    Dim lColumnR As Long
    Dim lRowT    As Long
    Dim lRowB    As Long

    'UsedRangeでデータの範囲を自動的に求めます
    Set oRange = ActiveSheet.UsedRange

    '範囲から、上下の行番号と左右の列番号を求めます
    ':上下
    lRowT = oRange.Row
    lRowB = oRange.Row + oRange.Rows.Count - 1
    ':左右
    lColumnL = oRange.Column
    lColumnR = oRange.Column + oRange.Columns.Count - 1

    '最右列と最終行を求めた結果
    MsgBox "最右列は : " & lColumnR
    MsgBox "最終行は : " & lRowB

    '最左列と開始行を求めた結果
    MsgBox "最左列は : " & lColumnL
    MsgBox "開始行は : " & lRowT

End Sub

図 7-2-2求めた範囲を保存した変数について
lRowT
範囲の一番上の行番号です。Rowプロパティの値をそのまま使います。

lRowB
範囲の一番下の行番号です。RowとRows.Countプロパティを加算し1を引きます。
Rows.Countは範囲の行数を返すため、そのまま加算すると底の位置が1行ずれてしまいます。1を引くのは、そのずれを無くすためです。

lColumnL
範囲の左端の列番号です。Columnプロパティの値をそのまま使います。

lColumnR
範囲の右端の列番号です。ColumnとColumns.Countプロパティを加算し1を引きます。
考え方はlRowBと同じです。

Copyright(C) 1999-2014 結城圭介(Ver.3.51) All rights reserved