VBA データベースの最終行を調べる
Excel VBAを使ってデータベースを集計するとき、レコードの最終行を知る必要がある。
私が最初に覚えたのがCurrentRegionプロパティで、例えば
GYO = Sheets("Data").Cells(1, 1).CurrentRegion.Rows.Count
Dataシートの1行目からデータが入っているものに有効な最終行調査である。
ところが、データベースを作る人によっては視覚的に見やすくするため空白行を挿入していたり、下記のように表題、凡例が説明されているようなものでは不都合がおきる。
A | B | C | |
1 | 統計名: | 基礎データ | |
2 | 凡例 | *** | 未調査 |
3 | - | データなし | |
4 | X | 非公表 | |
5 | |||
6 | 地域 | 教育 | 幼稚園数 |
7 | 北海道 | 422 | |
8 | 青森県 | 88 | |
9 | 岩手県 | 97 | |
10 | 宮城県 | 242 | |
11 | 秋田県 | 40 | |
12 | 山形県 | 74 | |
13 | 福島県 | 264 | |
14 | 茨城県 | 268 | |
15 | 栃木県 | 100 | |
16 | 群馬県 | 155 | |
17 | 埼玉県 | 544 | |
18 | 千葉県 | 510 |
gyo1 = Sheets("Data").Cells(1, 1).CurrentRegion.Rows.CountSub Macro4()
gyo2 = Sheets("Data").Cells(6, 1).CurrentRegion.Rows.Count
gyo3 = Sheets("Data").Cells(6, 2).CurrentRegion.Rows.Count
gyo4 = Sheets("Data").Range("A65536").End(xlUp).Row
gyo5 = Sheets("Data").Range("B65536").End(xlUp).Row
gyo6 = Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
gyo6 = Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
gyo7 = Sheets("Data").Cells(Rows.Count, 2).End(xlUp).Row
gyo7 = Sheets("Data").Cells(Rows.Count, "B").End(xlUp).Row
End Sub
実行結果 | ||
gyo1 | 4 | 表題案例部分をDBと判断 |
gyo2 | 13 | データ部分の行数 6行~18行 |
gyo3 | 13 | |
gyo4 | 18 | A列の最終行(※) |
gyo5 | 6 | B列の最終行(※) |
gyo6 | 18 | A列の最終行 |
gyo7 | 6 | B列の最終行 |
(※)Excel2003まではExcelの行数は65,536行であったがExcel2007以降では
1,048,576行まで拡張しているため65,536行を超えるようなデータの場合は使えない(65536行がデータ内のためデータの最上行を表示)。
データベースの構成、レコード(行)フィールド(列)などを見極めて使う必要がある。
<参考>6行目がデータのKey列の場合の最終列を調べる
retu1 = Sheets("Data").Cells(6, Columns.Count).End(xlToLeft).Column