【プログラミング】エクセルVBAで最終行や最終列を取得する

excelvba_blog_eye_catching_imagevba

エクセルの表を処理するマクロで表が更新されると、対応する行数や列数が変わってしまうため、もし仮に行や列を直値で設定してしまうと、表に新たな行や列が追加される度にコードの更新が必要になります。これではかえって手間が増えてしまうため、プログラムを書いて効率的にしたり、自動化したとは言えなくなってしまいます。

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

こういった場合の対処ができるようにVBAで表の最終行最終列を取得して、処理する時点での表の大きさ(行や列)に応じられます。

ここではエクセルの表の最終行や最終列の取得方法について紹介します。

独学だと中々スキルが身についた実感が湧かない。学習環境を見直してみませんか?

エクセルで繰り返しや転記作業で苦しい思いをした経験はありませんか?
今まで苦労してきたその作業を簡単なプログラムをおぼえるだけで解決できる可能性があります。
なるべくお金や時間をかけずにエクセルマクロVBAを習得したい人にはこちらの「1日速習講座」がおすすめです。

  1. エクセルの最終行と最終列を取得する方法
    1. .Cells(Rows.Count, 1).End(xlUp).Row
    2. .UsedRange.Rows.Count
    3. .Cells(1, Columns.Count).End(xlToLeft).Column
    4. .UsedRange.Columns.Count
    5. UsedRangeプロパティをつかう場合の注意点
    6. VBAで最終行・最終列を正しく取得されているか確認する
      1. 最終行を取得したときにかえってくる値
      2. .End(xlUp).Rowで最終行を取得する理由
      3. 最終列を取得したときにかえってくる値
      4. .End(xlToLeft).Columnで最終列を取得する理由
  2. 最終行まで取得して範囲内の合計値や平均値を算出する
    1. WorksheetFunctionのCount関数
    2. WorksheetFunctionのCountif関数
      1. 氏名に特定の文字を含む数を求める
    3. WorksheetFunctionのCountifs関数
    4. WorksheetFunctionのSum関数
    5. WorksheetFunctionのSumIf関数
    6. WorksheetFunctionのSumIfs関数
    7. WorksheetFunctionのMin関数
    8. WorksheetFunctionのMax関数
    9. WorksheetFunctionのMedian関数
    10. WorksheetFunctionのAverage関数
    11. WorksheetFunctionのAverageIf関数
    12. WorksheetFunctionのAverageIfs関数
  3. Withステートメントやオブジェクト変数でコードの可読性を良くする
    1. Withステートメントをつかう
      1. WithステートメントをつかったCountIfs関数のコード
      2. WithステートメントをつかったSumIfs関数のコード
      3. WithステートメントをつかったAverageIfs関数のコード
    2. 関数で使用する範囲(エリア)をオブジェクト変数に代入する
  4. 「エクセル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列目以外の行数を取得したい場合は、適宜書きかえることで対応可能です。
例えば、ActiveSheetThisWorkbook.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行目以外の行数を取得したい場合は、適宜書きかえることで対応可能です。
例えば、ActiveSheetThisWorkbook.Worksheets(“Sheet1”)などに書きかえることでシートを固定したり、(1, Columns.Count)1の部分が行になるので、5行目の最終列を取得したければ5に書きかえれば変更できます。

.UsedRange.Columns.Count

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

ActiveSheet.UsedRange.Columns.Count

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

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

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

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

Excelワークシート上の表です。サンプルデータは架空の情報です。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で最終行と最終列が取得できているか見ていきます。

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

Excelワークシート上の表です。サンプルデータは架空の情報です。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)と指定するのか。

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

Excelワークシート上の表です。サンプルデータは架空の情報です。RangeオブジェクトのEndプロパティのType引数の内、xlDownと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が返ってくるようになります。

Excelワークシート上の表です。サンプルデータは架空の情報です。RangeオブジェクトのEndプロパティのType引数の内、xlToLeftとxlToRightがどのような動きとなるのかを説明するための画像です。

独学の学習効率でお悩みの人必見!
<動画学習見放題サービス>

初心者にやさしいチューターなら今すぐにはじめられる

オンラインで学習したい
プログラミングで副業をはじめたい
パソコン・エクセルの学習をしたい

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

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

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

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

Excelワークシート上の表です。サンプルデータは架空の情報です。VBAで最終行や最終列の取得してできるプログラムを紹介する上で説明に用いる画像です。

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)
※範囲内のセルで複数条件に一致する値が入力されている個数を数値で返します。尚、試してみたことはありませんが、条件は最大で127個設定可能とのこと。

実行結果

4

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

WorksheetFunctionのSum関数

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

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

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

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”)

SumIfs関数のつかいかた

変数 = WorksheetFunction.SumIfs(合計範囲, 範囲1,条件1,範囲2,条件2)
※範囲内の条件に一致したものの合計値を数値で返します。Sumif関数とは異なり、引数に合計範囲を先に記述するところがポイントです。尚、試してみたことはありませんが、条件は最大で127個設定可能とのこと。

実行結果

566

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, 22, 25, 27, 29, 32, 33, 36, 40, 41

これを昇順に並べると、

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)
※複数の条件に一致する範囲の数値の平均値を返します。Averageifs関数も、Countifs関数やSumifs関数と同様に平均を求める範囲を最初の引数に指定します。尚、試してみたことはありませんが、条件は最大で127個設定可能とのこと。

実行結果

 58

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

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ステートメントをつかったCountIfs関数のコード

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

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ステートメント利用なし
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ステートメントを利用

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

変数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
Withステートメント利用なし
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ステートメントを利用

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

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ステートメント利用なし
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ステートメント利用

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

こちらは、ワークシート関数で指定する範囲をオブジェクト変数に代入する方法となります
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

赤文字の部分がポイントとなる箇所ですが、まずはセル範囲を代入するオブジェクト変数を準備します。ここではtargetsearchをRange型として宣言しています。
そしてAverageIfs関数で平均を求める範囲をtargetに代入、条件を検索する範囲をsearchに代入した上で、AverageIfs関数の引数にそれぞれの変数を指定したことでコードを短文化しています。


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

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

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

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

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

UsedRangeプロパティ

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

ワークシート関数をつかって指定したデータの個数を数える

ワークシート関数をつかって範囲内の数値を合計する

ワークシート関数をつかって範囲内の数値の平均を取得する

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

Withステートメントをつかって作業対象範囲を参照できるようにする

変数オブジェクトに代入し、作業対象範囲を参照できるようにする


今回はここまで。

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

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

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

コダマのもりブログはにほんブログ村に登録しています。
今回の記事が役に立ったと感じて頂けたら、フォローお願い致します。

コメント

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