エクセルVBA｜表の最終行・列を取得してマクロの実行範囲に指定する

エクセルの表を処理するマクロで表が更新されると、対応する行数や列数が変わってしまうため、もし仮に行や列を直接固定の値を指定してしまうと、表に新たな行や列が追加される度にコードの更新が必要になります。

例えば、何かの処理を5行目まで実行するとか、5回繰り返すなど。繰り返し回数や作業するセルの範囲などで決まった値を指定すると、マクロの柔軟性を欠いてしまう結果となり、その都度VBAコードを更新する必要があるため、本当の意味で効率化や自動化をした。とは言えなくなってしまいます。

実務においては、何かしらのデータをまとめた表の行数や列の数が増減することは日常的に発生することが一般的です。これに対応していないプログラムであれば、表の状態が変わるたびにプログラムを更新する作業が発生してしまうことから、結果として作業量が増加していまいます。

こういった場合の対処ができるようにエクセルVBAでは、表の最終行や最終列を取得して、処理するときの表の状態に合わせて処理を繰り返す回数や、範囲を指定する必要があります。

ここでは、エクセルVBAでエクセルワークシート上の表から、最終行や最終列の取得方法について紹介します。

エクセルワークシートの最終行と最終列を取得する方法

まずは、最終行を取得する場合の書き方はこちらになります。

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

RangeオブジェクトのEndプロパティを利用する方法

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

こちらのサンプルコードでは、アクティブシートの1列目の最終行を取得します。戻り値は引数で指定した列の行数の数値となり、.Cells(Rows.Count, 1)の部分がエクセルシートの1列目の行を数える.End(xlUp).Rowの最終行となります。

アクティブシートではなく、シート名を指定する場合や1列目以外の行数を取得したい場合は、引数を適宜書きかえることで対応可能です。

例えば、ActiveSheetをThisWorkbook.Worksheets(“Sheet1”)などに書きかえることでシートを固定したり、(Rows.Count, 1)の 1 の部分が列になるので、5列目の最終行を取得したければ 5 に書きかえれば変更できます。

.UsedRange.Rows.Count

WorksheetオブジェクトのUsedRangeプロパティを利用する方法

ActiveSheet.UsedRange.Rows.Count

VBAのWorksheetオブジェクトには、UsedRangeプロパティがあります。このプロパティを使用すると、Excelシート上で実際に使用されている範囲を取得できます。

UsedRangeプロパティを利用すると、Excelシートの範囲が空白や空セルであっても、実際に使用されている範囲を正確に取得します。たとえば、シート全体に対して行や列を挿入し、空白の範囲ができた場合でも、UsedRangeプロパティを使用すれば、実際に使用されている範囲を正確に取得できます。

UsedRangeプロパティの取得方法は、Worksheetオブジェクトに対してUsedRangeプロパティを使用します。戻り値は、Rangeオブジェクトとなります。

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

最終列を取得したいときはこちらになります。

RangeオブジェクトのEndプロパティを利用する方法

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

こちらのサンプルコードでは、アクティブシートの1行目の最終行を取得します。戻り値は数値となり、.Cells(1, Columns.Count)の部分がエクセルシートの1行目の行を数える.End(xlToLeft).Columnの最終列となります。

アクティブシートではなく、シート名を指定する場合や1行目以外の行数を取得したい場合は、適宜書きかえることで対応可能です。
例えば、ActiveSheetをThisWorkbook.Worksheets(“Sheet1”)などに書きかえることでシートを固定したり、(1, Columns.Count)の1の部分が行になるので、5行目の最終列を取得したければ5に書きかえれば変更できます。

.UsedRange.Columns.Count

WorksheetオブジェクトのUsedRangeプロパティを利用する方法

ActiveSheet.UsedRange.Columns.Count

行のときと同じ説明になるので割愛しますが、このプロパティを使用すると、Excelシート上で実際に使用されている範囲を取得できます。

UsedRangeプロパティをつかう場合の注意点

UsedRangeプロパティは、実際に使用されている範囲を取得するため、シート上に使用されている範囲よりも大きな範囲を返すことがある点に注意する必要があります。

例えば、以下のような表であった場合を見てみます。

注目点としては、赤色で塗りつぶしたセル(L16)があるところです。
この状態でUsedRangeプロパティをつかったコードで戻り値(使用範囲)を出力してみます。

Sub sample()

    Dim rng As Range
    Set rng = ActiveSheet.UsedRange
    
    ' 取得した範囲を出力する
    Debug.Print rng.Address

End Sub

実行結果

$A$1:$L$16

UsedRangeプロパティの戻り値を変数rngに代入して、Debug.Print rng.Addressでセル範囲をアドレス形式で出力されたものですが、結果は赤く塗りつぶしたセルの部分まで取得してしまっています。
このようにUsedRangeプロパティはセルに値が記入されていなくても、罫線などがあればその部分も使用範囲として見なし取得する点をおさえておきましょう。

VBAで最終行・最終列を正しく取得されているか確認する

以下の表があった場合に、VBAで最終行と最終列が取得できているか見ていきます。

テスト結果一覧をまとめた表

最終行を取得したときにかえってくる値

上記のテスト結果一覧をまとめた表でコードを実行してみます。

入力コード

Sub sample()

Dim lastRow As Long
lastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

Debug.Print (lastRow)

End Sub

実行結果

58

.Rowプロパティは、指定した範囲の最初のセルの行番号を返します。
テスト結果一覧をまとめた表の1行目は[ID、氏名、性別…]などの見出し情報となっています。それぞれのデータは2行目から記録されていて全部で57人分あることから、行数である58という数値がかえってきます。

つまりこちらの表の1列目(ID列)の最終行は58行目ということになります。

.End(xlUp).Rowで最終行を取得する理由

エクセルのワークシート上において、最終行というのは何かしらの値が入った一番下のセルと言えます。先ほどのテスト結果一覧をまとめた表のA1セルを選択した状態で、Ctrl ＋ ↓ の操作を実行するとA58セルに移動しますが、これをVBAで指示する場合、以下のように書きます。

.Cells(1, 1).End(xlDown).Select

または

.Range(“A1”).End(xlDown).Select

これで、.Cells(Rows.Count, 1).End(xlUp).Rowと同じセルを選択した状態となるのですが、ではなぜ最終行を取得する方法として、.End(xlDown)ではなく、.End(xlUp)と指定するのか。

それは、以下のような場合があるためです。

行ごとデータの削除があった場合は、空欄のひとつ上が最終行となってしまいます。これを回避するために、一度エクセルシートの一番下(シートの最終行)のA列（A1048576）セルに移動してから、Ctrl + ↑ 操作し、最終行を取得する方法を使います。

最終列を取得したときにかえってくる値

行のときと同様に、テスト結果一覧をまとめた表で最終列を取得するコードを実行してみます。

入力コード

Sub sample3()

Dim lastCol As Long
lastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

Debug.Print (lastCol)

End Sub

実行結果

12

.Columnプロパティは、指定した範囲の最初のセルの列番号を返します。
テスト結果一覧をまとめた表の1行目は見出し情報でL列までとなっていることから、エクセルワークシートの列数として12が返ってきています。

つまりこちらの表の1行目の最終列は12列目(L列)ということになります。

.End(xlToLeft).Columnで最終列を取得する理由

エクセルのワークシート上において、最終行というのは何かしらの値が入った一番下のセルと言えます。先ほどのテスト結果一覧をまとめた表のA1セルを選択した状態で、Ctrl ＋ → の操作を実行するとL1セルに移動しますが、これをVBAで指示する場合、以下のように書きます。

.Cells(1, 1).End(xlToLeft).Select

または

.Range(“A1”).End(xlToLeft).Select

ただし、これでは行の場合と同様に、エクセルの表で列ごとデータが空欄となっていた場合は、その手前までの列を最終列としてみなし取得することになります。

これを回避するために、一度エクセルシートの一番右(シートの最終列)のXFD列（XFD1）セルに移動してから、Ctrl + ← 操作をし、最終列を取得する方法を使います。

これも空白列がない場合に12が返ってきていたものが、7が返ってくるようになります。

最終行まで取得して範囲内の合計値や平均値を算出する

これまでの説明で、エクセルの表の最終行の取得ができるようになりました。では、ここからは最終行を取得してできることをいくつかサンプルで紹介していきます。

サンプルデータとなるエクセルの表は先ほど紹介した以下です。
改めて掲載しておきます。

テスト結果一覧をまとめた表

WorksheetFunctionのCount関数

まずは範囲セル内の数値が入力された個数を返してくれる関数となりますが、これを先ほど取得した最終行の値をつかってVBAのコードで書いてみます。

入力コード

Sub sample()

Dim ro As Long, col As Long
Dim ans As Long

ro = ThisWorkbook.Worksheets("テスト結果一覧").Cells(Rows.Count, 1).End(xlUp).Row
ans = WorksheetFunction.Count(ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 3), Cells(ro, 3)))
Debug.Print (ans)

End Sub

Count関数のつかいかた

変数 = WorksheetFunction.Count(範囲)

実行結果

57

こちらの入力コードと実行結果を基にここから詳しく解説していきます。

コードの解説

先にも紹介したとおり、最終行の行数を取得しているステートメントです。

ro = ThisWorkbook.Worksheets("テスト結果一覧").Cells(Rows.Count, 1).End(xlUp).Row

変数のroにテスト結果一覧シートのA列の最終行の行数(数値)を代入しています。
テスト結果一覧のA(ID)列の最終行は57の値が入力されているセルですが、1行目は見出しとしてIDが記入されているので、セルの番地で言えば“A58”セルです。つまり行数としては58行目です。したがって、変数roには58の数値が代入されることになります。

続いてワークシート関数のCount関数をつかうステートメントです。

ans = WorksheetFunction.Count(ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 3), Cells(ro, 3)))

Count関数は、引数に指定した範囲内で数値が入力されたセルの個数を返します。
サンプルコードでの範囲は、引数で指定している.Range(Cells(2, 3), Cells(ro, 3))です。Cellsのかっこ内は行, 列の順番で指定していることから、Count関数の範囲の開始位置は行数が2、列数は3(C列)となり、終点位置はroに代入された数値である58行目、列数は開始位置同様に3(C列)です。

つまりサンプルコードのCount関数では、C列(性別)に記入されている数値の個数を出力した実行結果として、57個の数値が入力されたセルの個数だったため、変数にansに57が代入されて、イミディエイトウィンドウに57が表示されるということになります。

WorksheetFunctionのCountif関数

Count関数の部分をCountif関数に変更して、性別に” 1 “(男性)が記入されている個数を出力してみます。その他の部分のコードはCount関数のときのものと全く同じです。

ans = WorksheetFunction.CountIf(ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 3), Cells(ro, 3)), 1)

CountIf関数のつかいかた

変数 = WorksheetFunction.Countif(範囲,条件)

実行結果

29

範囲内の” 1 “(男性)のセル数は29ということは、リスト上の29名は男性だということになります。

性別が” 2 “(女性)の個数もごらんのとおりです。

ans = WorksheetFunction.CountIf(ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 3), Cells(ro, 3)), 2)

実行結果

28

範囲内の” 2 “(女性)のセル数が28ということは、リスト上の28名は女性だということになります。

氏名に特定の文字を含む数を求める

今度は、B(氏名)列の範囲で特定の文字を含むセルの個数を数えてみます。

Countif関数の範囲をB列である2に変更して、氏名に文字列である” 田 “を含むセルの個数を数えてみます。

ans = WorksheetFunction.CountIf(ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 2), Cells(ro, 2)), "田")

実行結果

15

範囲内で” 田 “を含む氏名の人は15名だということになります。

WorksheetFunctionのCountifs関数

Countifs関数をつかって複数条件に一致するセルの個数を取得してみます。Countifs関数はCountif関数の上位互換となるので、こちらを使えるようにするとCountif関数の利用シーンで代用可能です。

ここでは、数学の点数が70点以上で、理科の点数が80点よりも大きい数値のデータの個数を求めてみます。

ans = WorksheetFunction.CountIfs(ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 7), Cells(ro, 7)), ">=70", ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 9), Cells(ro, 9)), ">80")

Countifs関数のつかいかた

変数 = WorksheetFunction.Countifs(範囲1,条件1,範囲2,条件2)

実行結果

4

数学が70点以上、理科が80点よりも大きい点数の人は4人いることになります。

WorksheetFunctionのSum関数

Sum関数をつかって指定範囲内のセルの合計数値を取得してみます。

ここでは、全ての教科の合計をしている列(K列)の合計を求めてみます。

ans = WorksheetFunction.Sum(ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 11), Cells(ro, 11)))

2行目の11列のセルから、変数[ ro ]行目の11列の範囲をSum関数の引数として指定します。

Sum関数のつかいかた

変数 = WorksheetFunction.Sum(合計範囲)

実行結果

14305

表に記録されている、57名全員のテスト結果の合計値は14305点となります。

WorksheetFunctionのSumIf関数

SumIf関数をつかって特定の条件範囲で合計した数値を取得してみます。

ここでは、2年生の国語の合計点数をSumIf関数で求めてみましょう。

ans = WorksheetFunction.SumIf(ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 4), Cells(ro, 4)), "2", ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 6), Cells(ro, 6)))

SumIf関数のつかいかた

変数 = WorksheetFunction.SumIf(範囲, 条件, 合計範囲)

実行結果

658

2年生の国語の合計点数は658点ということになります。

WorksheetFunctionのSumIfs関数

SumIfs関数をつかって複数の条件に一致したものの合計数値を取得してみます。Sumifs関数はSumif関数の上位互換となるので、こちらを使えるようするとSumif関数の利用シーンで代用可能です。

ここでは、3年生の男性の英語のテスト結果を合計にしてみます。

ans = WorksheetFunction.SumIfs(ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 8), Cells(ro, 8)), ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 3), Cells(ro, 3)), "1", ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 4), Cells(ro, 4)), "3")

3列目は性別が数値で入力されているので、条件は1を指定、4列目の学年では、条件に3を指定します。
合計範囲を英語のテスト結果が入力されている8列目に指定します。

SumIfs関数のつかいかた

変数 = WorksheetFunction.SumIfs(合計範囲, 範囲1,条件1,範囲2,条件2)

実行結果

568

3年生の男性全員のテスト結果(英語)を合計すると566点となります。

WorksheetFunctionのMin関数

Min関数をつかって範囲内の最小値を取得してみます。

ここでは、平均点の最小値を求めてみましょう。

ans = WorksheetFunction.Min(ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 12), Cells(ro, col)))

Min関数のつかいかた

変数 = WorksheetFunction.Min(範囲)

実行結果

27

57名の5教科のテスト平均点で最小値は27点となります。

WorksheetFunctionのMax関数

Min関数とは真逆のMax関数でもっとも高かった平均点を求めてみます。

ans = WorksheetFunction.Max(ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 12), Cells(ro, col)))

Max関数のつかいかた

変数 = WorksheetFunction.Max(範囲)

実行結果

77

57名の5教科のテスト平均点で最大値は77点となります。

WorksheetFunctionのMedian関数

Median関数をつかって5教科平均の中央値を求めてみます。

中央値とは、データセットを昇順または降順に並べたときに、中央に位置する値のことを指します。
例えば、以下のような10名の年齢データがあるとします。

18, 32, 33, 36, 40, 41 ,22 , 25 , 27, 29

これを昇順に並べると、

18, 22, 25, 27, 29, 32, 33, 36, 40, 41

となります。
このとき、中央に位置する値は29となります。つまり、このデータの中央値は29歳ということになります。

中央値は、平均値とは異なり、外れ値（極端に大きい値や小さい値）の影響を受けにくいため、データの分布が歪んでいる場合には平均、平均値よりも代表値として適している場合があります。

それでは、テスト結果の平均点で中央値を求めるコードを書いてみましょう。

ans = WorksheetFunction.Median(ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 12), Cells(ro, col)))

Median関数のつかいかた

変数 = WorksheetFunction.Median(範囲)

実行結果

48

平均の中央値は48点ということになります。
L列(平均)の平均は50点ですので、中央値とは少しずれがあることが分かります。

WorksheetFunctionのAverage関数

Average関数をつかって範囲内の平均値を求めてみます。

ここでは、社会のテストの平均点数を算出してみましょう。

ans = WorksheetFunction.Average(ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 10), Cells(ro, 10)))

Average関数のつかいかた

変数 = WorksheetFunction.Average(範囲)

実行結果

51

社会のテスト結果の平均は51点ということになります。

WorksheetFunctionのAverageIf関数

AverageIf関数をつかって指定条件に一致した範囲の平均を求めてみます。

ここでは、2年生の5教科合計の平均を算出していきます。

ans = WorksheetFunction.AverageIf(ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 4), Cells(ro, 4)), 2, ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 11), Cells(ro, 11)))

AverageIf関数のつかいかた

変数 = WorksheetFunction.Averageif(範囲,条件,平均を求める範囲)

実行結果

234

2年生の5教科合計点数の平均は234点となります。

WorksheetFunctionのAverageIfs関数

AverageIfs関数をつかって複数の条件に一致する範囲で平均を求めてみます。AverageIfs関数はAverageIf関数の上位互換となるので、こちらを使えるするとAverageIf関数の利用シーンで代用可能です。

ここでは、1年生の女性で数学の平均点数を算出してきます。

ans = WorksheetFunction.AverageIfs(ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 7), Cells(ro, 7)), ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 3), Cells(ro, 3)), 2, ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 4), Cells(ro, 4)), 1)

AverageIfsのつかいかた

変数 = WorksheetFunction.Averageifs(平均を求める範囲,範囲1,条件1,範囲2,条件2)

実行結果

58

1年生の女性は数学の平均点が58点であることが求められました。

ここでは、エクセルで代表的なワークシート関数を実行するマクロの作りかたを紹介しました。

また、エクセルの実務ではよく使われる『 Vlookup関数 』を実行するマクロの作りかたについては、別の記事で紹介しています。VBAでVlookup関数を動かすマクロの作りかたを知りたい人は、ぜひ以下のリンクよりご覧ください。

Withステートメントやオブジェクト変数でコードの可読性を良くする

ここまでで、データを記録している表に行や列が変わったとしても、最終行・最終列を取得することで作業範囲を柔軟に拡大できることがわかりました。そして、その範囲を取得してワークシート関数を利用することで個数を数えたり、合計や平均を算出する方法を紹介しました。

しかし、今回ご紹介したワークシート関数にて範囲を指定したりすると、それぞれのコードが長文化してくるので、だんだんと読みにくくなってくるかと思います。

そこで、ここからは今回紹介した関数をつかったコードの内、長くなってしまいがちな関数3つをよりコンパクトにスッキリと書くための方法をご案内します。

Withステートメントをつかう

VBAのWithステートメントは、オブジェクトのプロパティやメソッドを簡単に参照できるようにするためのものです。Withステートメントを使うことで、同じオブジェクトに対する複数のプロパティやメソッドを一度に設定できます。

Withステートメントの構文

With オブジェクト
.プロパティ1 = 値1
.プロパティ2 = 値2
.メソッド1 パラメータ1, パラメータ2
.メソッド2 パラメータ3
End With

Withステートメントでは、With 〇〇 から End Withまでの間で、〇〇に書いたオブジェクト部分のコードを省略できるようになります。〇〇オブジェクトのプロパティやメソッドを処理する場合は、” . “から書きはじめます。

Withステートメントを利用して、[ThisWorkbook.Worksheets(“テスト結果一覧”)]を参照できるようにして書きかえた場合をみてみましょう。

以下、3つのサンプルコードは同じ実行結果となりますが、コード量は大きく異なってきます。Withステートメントについて詳しく書いた記事はこちらです。

WithステートメントをつかったCountIfs関数のコード

右側がWithステートメントを使用したものです。特にワークシート関数をつかう行は、引数の指定の数が多くなればなるほど文字量は増えていきます。Withステートメントを用いたことで、かなりコード量が減ってスッキリしたものになっていることで読みやすく感じます。

Withステートメントを使っていないCountIfs関数のコード

Sub sample_CountIfs()

Dim ro As Long, col As Long
Dim ans As Long

ro = ThisWorkbook.Worksheets("テスト結果一覧").Cells(Rows.Count, 1).End(xlUp).Row
col = ThisWorkbook.Worksheets("テスト結果一覧").Cells(1, Columns.Count).End(xlToLeft).Column

ans = WorksheetFunction.CountIfs(ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 7), Cells(ro, 7)), ">=70", ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 9), Cells(ro, 9)), ">80")

Debug.Print (ans)

End Sub

Withステートメントを使って書いたCountIfs関数のコード

Sub With_sample()

Dim ro As Long, col As Long
Dim ans As Long

With ThisWorkbook.Worksheets("テスト結果一覧")

ro = .Cells(Rows.Count, 1).End(xlUp).Row
col = .Cells(1, Columns.Count).End(xlToLeft).Column

ans = WorksheetFunction.CountIfs(.Range(Cells(2, 7), Cells(ro, 7)), ">=70", .Range(Cells(2, 9), Cells(ro, 9)), ">80")

End With

Debug.Print (ans)

End Sub

Withの行から、End Withまでの間のコードに注目してください。
変数roやcolに最終行や最終列の数値を代入していますが、書き方は以下のようになります。

●変数roに最終行数を代入するコード

ro = .Cells(Rows.Count, 1).End(xlUp).Row

●変数colに最終列数を代入するコード

col = .Cells(1, Columns.Count).End(xlToLeft).Column

続いて、ワークシート関数のCountIfの引数で範囲を指定する場合も、以下のように書くことができます。

WorksheetFunction.CountIfs(.Range(Cells(2, 7), Cells(ro, 7)), ">=70", .Range(Cells(2, 9), Cells(ro, 9)), ">80")

WithステートメントをつかったSumIfs関数のコード

Countifs関数同様に、Sumifs関数を書きかえると以下となります。

Sub sample_SumIfs()

Dim ro As Long, col As Long
Dim ans As Long

ro = ThisWorkbook.Worksheets("テスト結果一覧").Cells(Rows.Count, 1).End(xlUp).Row
col = ThisWorkbook.Worksheets("テスト結果一覧").Cells(1, Columns.Count).End(xlToLeft).Column


ans = WorksheetFunction.SumIfs(ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 8), Cells(ro, 8)), ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 3), Cells(ro, 3)), "1", ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 4), Cells(ro, 4)), "3")

Debug.Print (ans)

End Sub

Sub With_sample()

Dim ro As Long, col As Long
Dim ans As Long

With ThisWorkbook.Worksheets("テスト結果一覧")

ro = .Cells(Rows.Count, 1).End(xlUp).Row
col = .Cells(1, Columns.Count).End(xlToLeft).Column

ans = WorksheetFunction.SumIfs _
(.Range(Cells(2, 8), Cells(ro, 8)), .Range(Cells(2, 3), Cells(ro, 3)), "1", .Range(Cells(2, 4), Cells(ro, 4)), "3")

End With

Debug.Print (ans)

End Sub

WithステートメントをつかったAverageIfs関数のコード

AverageIfs関数の場合のコードもご覧のとおり。

Withステートメントを使っていないAverageIfs関数のコード

Sub sample_AverageIfs()

Dim ro As Long, col As Long
Dim ans As Long

ro = ThisWorkbook.Worksheets("テスト結果一覧").Cells(Rows.Count, 1).End(xlUp).Row
col = ThisWorkbook.Worksheets("テスト結果一覧").Cells(1, Columns.Count).End(xlToLeft).Column

ans = WorksheetFunction.AverageIfs(ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 7), Cells(ro, 7)), ThisWorkbook.Worksheets("テスト結果一覧").Range(Cells(2, 3), Cells(ro, 3)), 2)

Debug.Print (ans)

End Sub

Withステートメントを使って書いたAverageIfs関数のコード

Sub With_sample()

Dim ro As Long, col As Long
Dim ans As Long

With ThisWorkbook.Worksheets("テスト結果一覧")

ro = .Cells(Rows.Count, 1).End(xlUp).Row
col = .Cells(1, Columns.Count).End(xlToLeft).Column

ans = WorksheetFunction.AverageIfs(.Range(Cells(2, 7), Cells(ro, 7)), .Range(Cells(2, 3), Cells(ro, 3)), 2)

End With

Debug.Print (ans)

End Sub

関数で使用する範囲(エリア)をオブジェクト変数に代入する

こちらは、ワークシート関数で指定する範囲をオブジェクト変数に代入する方法となります。
Withステートメントと合わせて使うことでさらにコードを短くできます。

AverageIfs関数のコードを書きかえてみましょう。

Sub With_Variable_sample()

Dim target As Range
Dim search As Range
Dim ro As Long, col As Long
Dim ans As Long


With ThisWorkbook.Worksheets("テスト結果一覧")

ro = .Cells(Rows.Count, 1).End(xlUp).Row
col = .Cells(1, Columns.Count).End(xlToLeft).Column

Set target = .Range(Cells(2, 7), Cells(ro, 7))
Set search = .Range(Cells(2, 3), Cells(ro, 3))

ans = WorksheetFunction.AverageIfs(target, search, 2)

End With

Debug.Print (ans)

End Sub

赤文字の部分がポイントとなる箇所ですが、まずはセル範囲を代入するオブジェクト変数を準備します。ここではtarget・searchをRange型として宣言しています。

そして、AverageIfs関数で平均を求める範囲をtargetに代入、条件を検索する範囲をsearchに代入した上で、AverageIfs関数の引数にそれぞれの変数を指定したことでコードを短文化しています。

尚、オブジェクト変数に値を代入するときはSetキーワードを書く必要があります。
少し難解な話になるため、ここでは詳しい説明は省きますが、ざっくりと説明するならば『数値や文字列は値そのものを代入し、オブジェクトの場合は、オブジェクトへの参照が代入される仕組みである。』になります。

ここでは、オブジェクトの代入には、Setキーワードを書くとおぼえておけば問題ありません。

変数のデータ型や種類については、こちらの記事で紹介していますので合わせて読むことをおすすめします。

「エクセルVBAで最終行や最終列を取得する」まとめ

01.エクセルVBAで最終行や最終列を取得する方法は

• RangeオブジェクトのEndプロパティ
• UsedRangeプロパティ

02.エクセルVBAで最終行や最終列をつかってできることの代表例

• ワークシート関数をつかって指定したデータの個数を数える
• ワークシート関数をつかって範囲内の数値を合計する
• ワークシート関数をつかって範囲内の数値の平均を取得する

03.ワークシート関数プログラムをさらにスッキリと書く方法

• Withステートメントをつかって作業対象範囲を参照できるようにする
• 変数オブジェクトに代入し、作業対象範囲を参照できるようにする

---

今回はここまで。

エクセルVBAでは、エクセルワークシート上の作業の自動化も多くなるので、ワークシート上の最終行や最終列を取得してプログラムを書く機会が必然的に多くなります。特に実務上では、ワークシート上のデータ増減があることが当然なため、プログラムをその都度書き直すようなことが無いようにしておくことが必要です。

また、範囲を取得した後の処理として、ここではワークシート関数を紹介しましたが、繰り返し処理などにも利用できるので、正確に作業範囲を取得することは、エクセルVBAプログラミングのスキルアップには必要不可欠な部分と言えます。

便利に使える部分なので、是非何度も見返して覚えておきましょう。