Excel VBA

エクセルVBAの最終行取得の活用法を紹介【データベース入力に効果的】

エクセルVBAでプログラムを作る目的はさまざまです。
データ集計、データ作成、外部プログラム操作・・・エクセルVBAでできることはたくさんあります。

今回は、データベース入力にオススメの最終行取得の使い方を紹介します。

データベース入力に便利な【+1】

最終行を取得するとデータベース入力がラクになります。

そのヒントは【+1】です。
何が【+1】なのか・・詳しく紹介していきたいと思います。

最終行だけでは不十分

データベースに入力する時考えられるのはこのようなことです。

  • データベースの必要な項目に入力をしていく
  • 前に入力したデータに別のデータを上書きしない
  • 登録済みの行の下に新たなデータを追加していく

データベースに必要な項目選びは大変ですが、一度作ってしまえば、あとは機械的にデータを追加するだけです。

機械的にデータを追加する時、登録済みの行の下に新たなデータを追加したいところですが、VBAの最終行取得をするだけでは不十分です。

例えばこちらのコード
一見よさそうなのですが、実際に動かしてみると問題ありです。
(何度プログラムを実行してもデータが変わらないと思います)

Sub データ作成ダメなパターン()
'最終行を取得
  Dim lastRow As Long
  lastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

'データベースに入力
  Sheet1.Cells(lastRow, 1) = "データ1"
  Sheet1.Cells(lastRow, 2) = "データ2"
  Sheet1.Cells(lastRow, 3) = "データ3"
  Sheet1.Cells(lastRow, 4) = "データ4"
  Sheet1.Cells(lastRow, 5) = "データ5"
End Sub

取得した最終行が変わらないため、データベースに上書きされてしまいます。

詳しく説明します。

【lastRow」という変数(最終行番号を入れるための変数です)を用意したあと、最終行のA、B、C、D、E列の5項目に【データ1~5】という文字を入力するプログラムです。

プログラムを一回動かした時の画面がこちらです。
この場合、最終行【lastRow】には0が入っている(A列について下からデータのあるセルを探したら何も見つからないためです)状態です。

その後、データベースに入力するコードに移動します。
Sheet.Cells(lastRow,1)="データ1"は、先ほどの最終行0(0行目はないので、1行目になるようです)と1列目(A列のこと)に【データ1】という文字を入力します。
あとは、B~E列と列をずらしながらデータを入力していきます。
(最初は「プログラムが上手く動いているように感じると思います)

ですが、2回目以降このプログラムを動かすとデータに変化がありません。

その理由は、データを上書きしてしまうからです。

なぜ上書きしてしまうのでしょうか?
それは、最終行取得の仕組みをみるとよくわかります。

最終行取得する仕組みは、エクセルの一番下のセルに移動した後、【Ctrl】+【↑】を押した状態です。
つまり、データのあるセルの上に重なります。

(A1セルにデータが入力されている状態で、A列の一番下のセルから【Ctrl】+【↑】を押すと、A1セルに移動することが分かると思います)

A1セルに移動してしまうということは、最終行を【1行目】ととらえてしまっているということ。
最終行が1行目の状態で、データを入力してしまうと、入力済みのセルにデータを上書きすることになってしまいます。

これでは、せっかくのデータベース入力プログラムが上手く動作しません。

そこで最終行+1という選択

もし、手入力でデータベースを追加入力した場合、最終行は「既存のデータが入力されている行の一つ下の行」と当然に考えると思います。
プログラムでも手入力と同じ最終行にデータを入力すれば、データベースにどんどんデータを追加することができます。

これをプログラムで表すにはこちらのコードを書く必要があります。

Sub データ作成1()
'最終行を取得
  Dim lastRow As Long
  lastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row

'データベースに入力
  Sheet1.Cells(lastRow + 1, 1) = "データ1"
  Sheet1.Cells(lastRow + 1, 2) = "データ2"
  Sheet1.Cells(lastRow + 1, 3) = "データ3"
  Sheet1.Cells(lastRow + 1, 4) = "データ4"
  Sheet1.Cells(lastRow + 1, 5) = "データ5"
End Sub

先ほどのコードとの違いは、「データベースに入力」の部分です。

Sheet1.Cells(lastRow + 1, 1) = "データ1"
この【+1】が重要です。

lastRow(この場合行を表す変数)に【+1】をすることで、一つ下の行を表すことができます。

こちらのプログラムだと、lastRow(1行目を表す)に1をプラスすることになります。
そのため、2行目を意味します。

プログラムを実行すると2行目にデータが入ります。(もちろんプログラムを動かすごとに3,4,5・・・行目とデータベースを追加できます)

この調子でプログラムを動かしていけば、データベースをどんどん追加していくことができます。

For文は少し注意

先ほどのプログラムは、データを一行ごとに追加するだけでした。
これだけでも便利ですが、For分を使うと効果的にデータベース入力ができます。

例えば、こちらのプログラム
For分で10回繰り返すようにしました。

ただ、こちらのプログラム・・・ちょっと問題ありです。
(一見、しっかり動いているようですが、データの上書きが一部行われてしまっています)

Sub データ作成繰り返し追加()
'最終行を取得
  Dim lastRow As Long, i As Long
  lastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
'データベースに入力
  For i = 1 To 10
    Sheet1.Cells(lastRow, 1) = "データ1"
    Sheet1.Cells(lastRow, 2) = "データ2"
    Sheet1.Cells(lastRow, 3) = "データ3"
    Sheet1.Cells(lastRow, 4) = "データ4"
    Sheet1.Cells(lastRow, 5) = "データ5"
    lastRow = lastRow + 1
  Next
End Sub

プログラムを一回動かすごとに、10回データの入力ができるようになります。
一回目は良いのですが・・・2回目プログラムを動かすと、一行足りません)

注意点は「データベース入力後の 【lastRow = lastRow + 1】 です。

【+1】という記述は良いのですが・・【+1】をするタイミングに問題があります。
【データベースに入力】処理が終わってから、一行下にズラすので、プログラム実行後の最初一行は上書きされてしまいます。

For文を書くにはこの書き方

For分を書く時の最終行は

先ほどのコードに【+1】を付け加えて、最終行を取得をしただけですが、しっかり動くようになります。

ちなみに、このプログラムをそのまま動かすと1行目を飛ばしてデータ入力を始めてしまいます。
ですが、問題なしです。

1行目に項目を書いておけば、問題なく動作します。

Sub データ作成繰り返し追加1()
'最終行を取得
  Dim lastRow As Long, i As Long

'データベースに入力
  For i = lastRow To lastRow + 9
    lastRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row + 1
    Sheet1.Cells(lastRow, 1) = "データ1"
    Sheet1.Cells(lastRow, 2) = "データ2"
    Sheet1.Cells(lastRow, 3) = "データ3"
    Sheet1.Cells(lastRow, 4) = "データ4"
    Sheet1.Cells(lastRow, 5) = "データ5"
  Next
End Sub

こちらのプログラムを動かすとこのようなデータを入力できます。

あとは、入力するデータを取得できれば、データベース入力プログラムを作ることができます。

あとがき

今回は、データベース入力に便利な最終行取得方法を消化しました。

【Sheet1.Cells(Rows.Count, 1).End(xlUp).Row + 1 】のように+1を入力すると、データベースへの書き込みができるようになります。

データベース作成を仕組化+自動化することで、情報の宝庫ができあがりますので、早めに作っておきたいところです。

今、エクセルVBAを勉強中で「エクセルシートにデータベースを入力して作りたい」と思っている方は試してみてはいかがでしょうか?

-Excel VBA
-, , , , ,