photo credit: marcoverch LIDL Shopping carts via photopin (license)
みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
エクセルVBAを使ってバラバラの経費精算書データを集約するシリーズです。
前回の記事はコチラ。
フォルダの中のブック全てについて開くという繰り返し処理の作り方をお伝えしました。
今回ですが、この経費収集プロシージャについて、実行をしても最終行以降にデータを追加していけるように修正をします。
そのために、エクセル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月のデータの収集が以下のように完了しているとします。
それで、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
とします。これで、すでにあるデータの次の行から書き込みを開始するようになります。
以下の記事でお伝えしている通りです。
なお、こちらの方法は最終行や最終列が非表示になっているときにはうまく動作しませんので、ご注意くださいね。
てか、なんでさっきのステートメントで最終行の次の行数が指定できるんですかね？
ステートメントの意味、ちゃんと理解して使わないと…ですよね！
ということで、今回詳しく解説していきます。
Rows.Countとは
まず、「Rows.Count」。
この部分を見てみましょう。
Countってくらいですから、何かの数を勘定しているんですね。
イミディエイトウィンドウで、以下のように打って Enter してみましょう。
? Rows.Count
以下のように出力されますよね。
RowsはRowsプロパティで、全ての行を表すRangeオブジェクトを取得します。
鋭い方はお気づきだと思いますが、ExcelのシートのMAX行数です。
で、実はこの「Rows」の前に対象となるオブジェクトが省略されているのですが、その正体はActiveSheetです。
つまり、「Rows」により作業中のワークシートのすべての行をRangeオブジェクトとして取得します。
そして、そのRangeオブジェクトに、Countプロパティを使うことで、ワークシートのすべての行の行数、つまり1048576という整数を取得できるということになります。
結果的として、
は、1048576行目、つまりシートの最大行の1列目のセルを表すRangeオブジェクト、ということになります。
さて、Rangeオブジェクトに対するCountプロパティ…これが、なぜ「セルの数」ではなくて「行数」になるのか、これはけっこう深い問題そうですね…
機会があれば、お伝えしたいです。
Endプロパティで終端まで移動したセルを取得する
続いて、Endというプロパティがあります。
これは、指定したRangeオブジェクトから、キーボードでいう Ctrl + 方向キー の操作で移動した先のRangeオブジェクトを返します。
書式はこうです。
「移動する方向」には、以下のいずれかの定数を指定します。
| 定数 | 内容 |
|---|---|
| xlUp | 上方向 |
| xlDown | 下方向 |
| xlToLeft | 左方向 |
| xlToRight | 右方向 |
つまり、以下はシートの最大行の1列目のセルから Ctrl + ↑ で移動した先のRangeオブジェクトということになります。
実際にやってみるとわかりますが、以下の位置。つまり、データがある行の最終行の1列目セルになります。
RowプロパティでRangeオブジェクトの行番号を取得する
さあ、あと少しです。
最後のRowプロパティですね。対象はRangeオブジェクトです。
Rowプロパティは、Rangeオブジェクトの先頭行の行番号を返します。
今回は単体セルなので、そのセルの行番号ということになります。
ですから、以下はデータがある行の最終行の行番号、ということになりますね。
これにプラス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入門】Do While～Loop文で条件を満たす間繰り返し
- 【エクセルVBA入門】繰り返しを使ってデータの転記をするときの２つのポイント
- 【エクセルVBA入門】With文でプログラムをスッキリわかりやすく書く
- 【エクセルVBA入門】他のワークブックをWithで開く＆保存せずに閉じる
- 【エクセルVBA入門】フォルダやファイルを操作するFileSystemオブジェクトとその使い方
- 【エクセルVBA入門】For Each～Next文でフォルダ内のブック全てを開く方法
- 【エクセルVBA入門】シートのデータがある最終行番号を求めるステートメントを徹底解説
- 【エクセルVBA入門】開いたブック名から文字列を抽出して人為的なミスを回避する方法
- 【エクセルVBA入門】マクロを作るときに知っておきたいマスタデータのこと
- 【エクセルVBA入門】開いたブックのファイル名から番号を取り出して数値に変換する
- 【エクセルVBA入門】マクロでVlookupを使ってデータを検索する方法
- 【エクセルVBA入門】Vlookupメソッドを使ったときに発生するエラーを回避する方法
- 【エクセルVBA入門】エラーが発生したときに分岐処理を追加する方法
- 【エクセルVBA入門】オートフィルタや行の非表示で隠れている行を全て表示する
コメント
徹底解説とは、また大きく出ましたね。
Worksheetオブジェクト.Cells(Rows.Count, 1).End(xlUp).Rowは
自分で説明しているように、Ctrl+↑を再現したものですから
フィルタした環境で使用すると正確に最終行が取得できません。
つまり今回のようなデータにおいては使用すべきではありません。
いえ、使うのは構いませんが、デメリットや危険性を記載しないのはいかがなものかと思います。
匿名希望さん
コメントありがとうございます。
確かにおっしゃる通りですね。
記事内にコメントを入れさせていただきました。
今後とも弊ブログをよろしくお願いいたします。