【エクセルVBA入門】シートのデータがある最終行番号を求めるステートメントを徹底解説


row-end

photo credit: marcoverch LIDL Shopping carts via photopin (license)

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズです。

前回の記事はコチラ。

【エクセルVBA入門】For Each~Next文でフォルダ内のブック全てを開く方法
エクセルVBAを使ってバラバラの経費精算書のデータを集約するシリーズです。今回は、For Each~Next文でフォルダ内の複数のワークブックの全てを順番に参照して処理していく方法についてお伝えします。

フォルダの中のブック全てについて開くという繰り返し処理の作り方をお伝えしました。

今回ですが、この経費収集プロシージャについて、実行をしても最終行以降にデータを追加していけるように修正をします。

そのために、エクセルVBAで最終行を求めるステートメントについて徹底的に解説をしていきます。

では、行ってみましょう!

スポンサーリンク

前回のおさらい

まずおさらいからです。

マクロを記述しているブックには以下のようなデータを集めるための「経費データ」というシートがあります。

経費データフォーマット

各スタッフがそれぞれ作成した経費精算書が、同じフォルダの「data」というフォルダ配下にゴソっと格納されていて、その経費データを収集しようというものです。

例として太郎さんの経費精算書はこんな感じです。

経費精算書ひな形

それで、フォルダ内のファイルすべてについて開いて、データを収集するプログラムがコチラです。

Sub 経費精算データ取り込み()

Dim fso As FileSystemObject
Set fso = New FileSystemObject

Dim di As Long: di = 2
Dim f As File
For Each f In fso.GetFolder(ThisWorkbook.Path & "\data").Files
    With Workbooks.Open(f.Path)
        With .Worksheets(1)
        
            Dim month As Date: month = .Range("G4").Value
            Dim departmentName As String: departmentName = .Range("G6").Value
            Dim staffId As Long: staffId = .Range("G8").Value
            Dim staffName As String: staffName = .Range("G7").Value
            
            Dim i As Long: i = 12
            Do While .Cells(i, 1).Value <> ""
                wsData.Cells(di, 1).Value = month '1 対象月
                wsData.Cells(di, 2).Value = .Cells(i, 1).Value '2 日付
                wsData.Cells(di, 3).Value = ""  '3 部署No
                wsData.Cells(di, 4).Value = departmentName '4 部署
                wsData.Cells(di, 5).Value = staffId '5 社員No
                wsData.Cells(di, 6).Value = staffName '6 氏名
                wsData.Cells(di, 7).Value = .Cells(i, 2).Value '7 科目
                wsData.Cells(di, 8).Value = .Cells(i, 5).Value '8 摘要
                wsData.Cells(di, 9).Value = .Cells(i, 6).Value '9 金額
                wsData.Cells(di, 10).Value = .Cells(i, 7).Value '10 備考
                i = i + 1: di = di + 1
            Loop
        End With
        .Close
    End With
Next f

End Sub

フォルダを取り扱うために、FileSystemオブジェクトを使っています。

また、For Each~Nextでフォルダ内のすべてのファイルについて処理をする繰り返しの中でデータの転記を行っています。

月が変わっても続きにデータを追加していきたい

さて、新しい月になったらその分の新たな経費精算書が集まってきますね。

例えば、2018年7月のデータの収集が以下のように完了しているとします。

7月分の収集が完了している経費データシート

それで、dataフォルダの中に2018年8月分の経費精算書を集めて、経費精算データ取り込みのプロシージャを実行してみます。

すると…以下のように、7月のデータの2行目から、8月のデータがある分だけ上書きになっちゃうんですね。

シートに追加したデータが上書きされてしまった

データ、新たに9行目から追加していいですよね…

データの最終行を求める

プログラムでいうと6行目。

「経費データ」シートのセル位置をつかさどるカウント変数diの開始位置が

Dim di As Long: di = 2

と、2行目にセットされているのが原因ですね。そりゃそうです。

毎回、実行のたびに2行目からの書き込みになってしまいます。

従ってこれを

Dim di As Long: di = wsData.Cells(Rows.Count, 1).End(xlUp).Row + 1

とします。これで、すでにあるデータの次の行から書き込みを開始するようになります。

以下の記事でお伝えしている通りです。

【初心者向けエクセルVBA】行の数をカウントする&不要な行を隠す
今回は行数をカウントする、行を隠す、などの「行を取り扱うテクニック」を紹介しています。いずれもデータや帳票を扱ったエクセルVBAではかなり重宝するテクニックですので、知っておいて損はありませんよ。

なお、こちらの方法は最終行や最終列が非表示になっているときにはうまく動作しませんので、ご注意くださいね。

てか、なんでさっきのステートメントで最終行の次の行数が指定できるんですかね?

ステートメントの意味、ちゃんと理解して使わないと…ですよね!

ということで、今回詳しく解説していきます。

Rows.Countとは

まず、「Rows.Count」

この部分を見てみましょう。

Countってくらいですから、何かの数を勘定しているんですね。

イミディエイトウィンドウで、以下のように打って Enter してみましょう。

? Rows.Count

以下のように出力されますよね。

イミディエイトウィンドウにRows.Countを出力
RowsはRowsプロパティで、全ての行を表すRangeオブジェクトを取得します。

鋭い方はお気づきだと思いますが、ExcelのシートのMAX行数です。

で、実はこの「Rows」の前に対象となるオブジェクトが省略されているのですが、その正体はActiveSheetです。

つまり、「Rows」により作業中のワークシートのすべての行をRangeオブジェクトとして取得します。

そして、そのRangeオブジェクトに、Countプロパティを使うことで、ワークシートのすべての行の行数、つまり1048576という整数を取得できるということになります。

Rows.Count

結果的として、

Worksheetオブジェクト.Cells(Rows.Count, 1)

は、1048576行目、つまりシートの最大行の1列目のセルを表すRangeオブジェクト、ということになります。

さて、Rangeオブジェクトに対するCountプロパティ…これが、なぜ「セルの数」ではなくて「行数」になるのか、これはけっこう深い問題そうですね…

機会があれば、お伝えしたいです。

Endプロパティで終端まで移動したセルを取得する

続いて、Endというプロパティがあります。

これは、指定したRangeオブジェクトから、キーボードでいう Ctrl + 方向キー の操作で移動した先のRangeオブジェクトを返します。

書式はこうです。

Rangeオブジェクト.End(移動する方向)

「移動する方向」には、以下のいずれかの定数を指定します。

定数 内容
xlUp 上方向
xlDown 下方向
xlToLeft 左方向
xlToRight 右方向

つまり、以下はシートの最大行の1列目のセルから Ctrl + で移動した先のRangeオブジェクトということになります。

Worksheetオブジェクト.Cells(Rows.Count, 1).End(xlUp)

実際にやってみるとわかりますが、以下の位置。つまり、データがある行の最終行の1列目セルになります。

シートのデータがある行の最終行の1列目のセル

RowプロパティでRangeオブジェクトの行番号を取得する

さあ、あと少しです。

最後のRowプロパティですね。対象はRangeオブジェクトです。

Rangeオブジェクト.Row

Rowプロパティは、Rangeオブジェクトの先頭行の行番号を返します。

今回は単体セルなので、そのセルの行番号ということになります。

ですから、以下はデータがある行の最終行の行番号、ということになりますね。

Worksheetオブジェクト.Cells(Rows.Count, 1).End(xlUp).Row

これにプラス1した行番号の行に、新たなデータを書き込み始めればよいということになりますよね。

経費データを追加していくプロシージャ

以上を踏まえて、修正した経費データ収集プロシージャがコチラです。

Sub 経費精算データ取り込み()

Dim fso As FileSystemObject
Set fso = New FileSystemObject

Dim di As Long: di = wsData.Cells(Rows.Count, 1).End(xlUp).Row + 1
Dim f As File
For Each f In fso.GetFolder(ThisWorkbook.Path & "\data").Files
    With Workbooks.Open(f.Path)
        With .Worksheets(1)
        
            Dim month As Date: month = .Range("G4").Value
            Dim departmentName As String: departmentName = .Range("G6").Value
            Dim staffId As Long: staffId = .Range("G8").Value
            Dim staffName As String: staffName = .Range("G7").Value
            
            Dim i As Long: i = 12
            Do While .Cells(i, 1).Value <> ""
                wsData.Cells(di, 1).Value = month '1 対象月
                wsData.Cells(di, 2).Value = .Cells(i, 1).Value '2 日付
                wsData.Cells(di, 3).Value = ""  '3 部署No
                wsData.Cells(di, 4).Value = departmentName '4 部署
                wsData.Cells(di, 5).Value = staffId '5 社員No
                wsData.Cells(di, 6).Value = staffName '6 氏名
                wsData.Cells(di, 7).Value = .Cells(i, 2).Value '7 科目
                wsData.Cells(di, 8).Value = .Cells(i, 5).Value '8 摘要
                wsData.Cells(di, 9).Value = .Cells(i, 6).Value '9 金額
                wsData.Cells(di, 10).Value = .Cells(i, 7).Value '10 備考
                i = i + 1: di = di + 1
            Loop
        End With
        .Close
    End With
Next f

End Sub

実行すると、以下のように実行を重ねてもデータを追加していくことができているのが確認できます。

エクセルVBAで経費データを最終行の次の行以降に追加した

まとめ

以上、エクセルVBAで最終行を求めるステートメントについて詳しく解説をしてきました。

長いステートメントですが、ひとつずつ紐解くとその意味はわかってきますよね。

さて、次回は人による入力ミスをいかにして防ぐか…という観点で、ファイル名を活用してみたいと思います。

【エクセルVBA入門】開いたブック名から文字列を抽出して人為的なミスを回避する方法
エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズです。人が入力する場合は必ず人為的なミスが入り込みます。それ回避する方法として開いたブックからファイル名を取得して利用する方法をお伝えします。

どうぞお楽しみに!

連載目次:エクセルVBAで経費データをデータベースに集約する

請求書シリーズと逆のパターンですが、バラバラの帳票からデータ一覧つまりデータベースに情報を集めて蓄積していく、というお仕事も多いと思います。ここでは各担当者から提出された経費精算書をデータベースに蓄積するプログラムを目標にして進めていきます。
  1. 【エクセルVBA入門】バラバラの経費精算書をデータにまとめる
  2. 【エクセルVBA入門】Do While~Loop文で条件を満たす間繰り返し
  3. 【エクセルVBA入門】繰り返しを使ってデータの転記をするときの2つのポイント
  4. 【エクセルVBA入門】With文でプログラムをスッキリわかりやすく書く
  5. 【エクセルVBA入門】他のワークブックをWithで開く&保存せずに閉じる
  6. 【エクセルVBA入門】フォルダやファイルを操作するFileSystemオブジェクトとその使い方
  7. 【エクセルVBA入門】For Each~Next文でフォルダ内のブック全てを開く方法
  8. 【エクセルVBA入門】シートのデータがある最終行番号を求めるステートメントを徹底解説
  9. 【エクセルVBA入門】開いたブック名から文字列を抽出して人為的なミスを回避する方法
  10. 【エクセルVBA入門】マクロを作るときに知っておきたいマスタデータのこと
  11. 【エクセルVBA入門】開いたブックのファイル名から番号を取り出して数値に変換する
  12. 【エクセルVBA入門】マクロでVlookupを使ってデータを検索する方法
  13. 【エクセルVBA入門】Vlookupメソッドを使ったときに発生するエラーを回避する方法
  14. 【エクセルVBA入門】エラーが発生したときに分岐処理を追加する方法
  15. 【エクセルVBA入門】オートフィルタや行の非表示で隠れている行を全て表示する

コメント

  1. 匿名希望 より:

    徹底解説とは、また大きく出ましたね。

    Worksheetオブジェクト.Cells(Rows.Count, 1).End(xlUp).Rowは
    自分で説明しているように、Ctrl+↑を再現したものですから
    フィルタした環境で使用すると正確に最終行が取得できません。

    つまり今回のようなデータにおいては使用すべきではありません。
    いえ、使うのは構いませんが、デメリットや危険性を記載しないのはいかがなものかと思います。

    • 匿名希望さん

      コメントありがとうございます。
      確かにおっしゃる通りですね。

      記事内にコメントを入れさせていただきました。

      今後とも弊ブログをよろしくお願いいたします。

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