入力済みのセルの最終行の下に新しく入力する方法

エクセルVBAセルの操作 セルの操作

エクセルの最終行に追加で入力する時に、画面スクロールするのが面倒って思った事ありませんか?

VBAを使わなくてもエクセルの標準機能でマウスを使ってスクロールをせずにデータ入力済み領域の最終端に一瞬で移動する方法ありますよね。

スポンサーリンク

マウスやキーボードで最終行へ瞬間移動

エクセルの標準機能なので、特別な設定がいらない2つの方法があります。

ショートカットでctrlキーを押しながら【矢印キー】を押す

この方法意外と知らない人多いですけど、簡単ですね。

次に、たまたまできたので正規の方法かどうかは分かりませんが、こんな下記の方法でも上のと同じようにできました。

  1. データが入力されているリストや表の適当なセルをクリック
  2. 選択されたセルに表示された枠の下の部分をダブルクリック
波乗りアヒル
波乗りアヒル

少し分かりにくいので、画像を参考にして下さい

同じ要領で、枠の上の部分をダブルクリックすると最上段へ、右ならば右枠に移動します。

スポンサーリンク

VBAコードを使ってセルの最終行にデータを追記する

エクセルで作る住所録では、新規登録をするにも最終行に自動的に追記できるようにしなくては使い物になりません。

そこでユーザーフォームに配置したコマンドボタンを押すことによって、自動的にセルの最終行にデータの記載ができるようにします。

Offsetオブジェクトをシンプルに使う

上図を参考にすると、この画面上の最終行は2列目なので、最終行のデータ入力はセルA2からB2,C2となりますので、シンプルにこのようなVBAコードを書くと・・・

エラーになる事もあるVBAコード

Private Sub CommandButton1_Click()
Range(“A1”).End(xlDown).Offset(1, 0).Value = TextBox1.Value
End Sub

セルA1の1行下にテキストボックス1の値を転記するようにVBAコードは書いたのですが・・・

ところが、下の図のように1行目を空けておくと問題なくできるようになります。

理由は単純で、セルA1の下の行にデータが入力されている時に、最終入力行の1行下に新しく追記するというVBAコードなのです。

その為に1行目に項目あって、初めてデータを入力する時のみセルA1の下が未入力なので、入力行の下が無い状態なのでエラーになります。

2つ目の図のように1番最初の行を使わないということができるならば、いいのですが、そうもいかない時もあるので、対処する方法を使いましょう。

空欄の時(最初のデータの時)の対処法

セルA2にデータが入っていればエラーにならないので、A2が空欄の時は・・・という条件を付け加えれば問題は解決できます。

最終行に追記する

Private Sub CommandButton1_Click()
If Range(“A2”).Value = “” Then
Range(“A1”).Offset(1, 0).Value = CommandButton1.Caption
Else
Range(“A1”).End(xlDown).Offset(1, 0).Value = CommandButton1.Caption
End If
End Sub

VBA解説
  1. コマンドボタン1がクリックされたら
  2. もしセルA2が空欄だったら
  3. セルA1の1つ下のセルにコマンドボタンの表示文字を入力する
  4. そうでなかったら(セルA2にデータが入っていたら)
  5. セルA1の行で入力済み最終行の1つ下のセルにコマンドボタンの表示文字を入力する
  6. Ifステートメント終了
  7. マクロ記録終了

ここではユーザーフォームを使った自作カレンダーで使用している日付ボタンをクリックしたら、目的のセルに追記するようにしているVBAコードです。

その為、コマンドボタンの表示文字になっていますが、テキストボックス(TextBox1.value)にして入力した文字を追記することもできますよ。

2つの項目をそれぞれ最終行にデータ入力

A列に「顧客番号」B列に「氏名」を一括で処理するには、単純に2つ並べちゃえばできますよ。

2つの項目をそれぞれ最終行へ

Private Sub CommandButton1_Click()
Range(“A1”).End(xlDown).Offset(1, 0).Value = TextBox1.Value
Range(“B1”).End(xlDown).Offset(1, 0).Value = TextBox2.Value
End Sub

このように単純にできますが、やはり2行目空欄だとエラーになるので先ほどと同じようにエラー対策を忘れずにVBAコード追加してください。

とりっぷぼうる
とりっぷぼうる

ちなみに最終行追加ができるようになったら、最終列も使えたら便利ですよ。

しかも「データ検索して同じ項目の行の右側に追加する」

条件を付けて最終行に複数一括データ入力

エクセル住所録で実際に使っているVBAコードは、制作者である自分以外のスタッフが使うので想定外の操作をされることがあるので、入力漏れ項目があるまま新規登録ボタンを押された時の処理なども必要です。

住所録入力フォーム
住所録入力フォーム
スポンサーリンク

そこで、条件を付けてたりエラーメッセージを準備してVBAコードを書いています。

最終行に追記する時のポイント
  1. 画面チラつき防止処理
  2. 空欄の時メッセージで警告
  3. 重複番号の時メッセージで警告
  4. 最初の行が空欄の時はその行にデータ入力
  5. 最初の行にデータがある場合は1行下にデータ入力
  6. 連番を自動入力する

このポイントを取り入れて最終行にデータを入力する住所録で使っているVBAコードはこれです。

条件付き最終行追記VBAコード

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
If UserForm1.TextBox1 = “” Then
MsgBox (“顧客番号が入力されていません”)
Else
If Range(“S11”).Value = 1 Then
MsgBox (“顧客番号が重複しています”)
Else
If Range(“AB1”).Offset(1) = “” Then
Range(“AB1”).Offset(1).Value = TextBox1.Value
Range(“AB1”).Offset(1, 1).Value = TextBox2.Value
Range(“AB1”).Offset(1, 2).Value = TextBox3.Value
Range(“AB1”).Offset(1, 3).Value = TextBox4.Value
Range(“AB1”).Offset(1, 4).Value = TextBox5.Value
Range(“AB1”).Offset(1, 5).Value = TextBox6.Value
Range(“AB1”).Offset(1, 6).Value = TextBox7.Value
Range(“AB1”).Offset(1, 7).Value = TextBox8.Value
Range(“AB1”).Offset(1, 11).Value = Label28.Caption
Range(“AB1”).Offset(1, 8).Value = Cells(2, 16).Value
If OptionButton4.Value = True Then
Range(“AB1”).Offset(1, 9).Value = (“男”)
ElseIf OptionButton5.Value = True Then
Range(“AB1”).Offset(1, 9).Value = (“女”)
End If
Else
Range(“AB1”).End(xlDown).Offset(1).Value = TextBox1.Value
Range(“AC1”).End(xlDown).Offset(1).Value = TextBox2.Value
Range(“AD1”).End(xlDown).Offset(1).Value = TextBox3.Value
Range(“AE1”).End(xlDown).Offset(1).Value = TextBox4.Value
Range(“AF1”).End(xlDown).Offset(1).Value = TextBox5.Value
Range(“AG1”).End(xlDown).Offset(1).Value = TextBox6.Value
Range(“AH1”).End(xlDown).Offset(1).Value = TextBox7.Value
Range(“AI1”).End(xlDown).Offset(1).Value = TextBox8.Value
Range(“AM1”).End(xlDown).Offset(1).Value = Label28.Caption
Range(“AJ1”).End(xlDown).Offset(1).Value = Cells(2, 16).Value
If OptionButton4.Value = True Then
Range(“AK1”).End(xlDown).Offset(1).Value = (“男”)
ElseIf OptionButton5.Value = True Then
Range(“AK1”).End(xlDown).Offset(1).Value = (“女”)
End If
End If
Dim i As Integer
i = 1
Do While Cells(i + 1, “AB”).Value <> “”
Cells(i + 1, “AA”).Value = i
i = i + 1
Loop
End If
End If
Unload UserForm1
Application.ScreenUpdating = Ture
End Sub

VBA解説
  1. コマンドボタン1が押されたら
  2. 画面更新処理を停止する
  3. もしユーザーフォーム1のテキストボックス1(顧客番号)が空欄だったら
  4. 「顧客番号が入力されていません」とメッセージを表示
  5. そうでなかったら(入力されていたら)
  6. セルS11が1だったら
  7. 「顧客番号が重複しています」とメッセージを表示
  8. そうでなかったら(重複せず新規番号ならなば)
  9. もしセルAB1の1行下が空欄だったら
  10. セルAB1の1行下にテキストボックス1の値を入力
  11. セルAB1の1行下、1列右(AC2)にテキストボックス2の値を入力
  12. セルAB1の1行下、2列右(AD2)にテキストボックス3の値を入力
  13. セルAB1の1行下、3列右(AE2)にテキストボックス4の値を入力
  14. セルAB1の1行下、4列右(AF2)にテキストボックス5の値を入力
  15. セルAB1の1行下、5列右(AG2)にテキストボックス6の値を入力
  16. セルAB1の1行下、6列右(AH2)にテキストボックス7の値を入力
  17. セルAB1の1行下、7列右(AI2)にテキストボックス8の値を入力
  18. セルAB1の1行下、11列右(AM2)にラベル28(更新状況)の表示文字を入力
  19. セルAB1の1行下、8列右(AJ2)にセルP2(更新日)の値を入力
  20. もしオプションボタン4が選択されたら
  21. セルAB1の1行下、9列右(AK2)に”男”を入力
  22. そうではなくもしオプションボタン5が選択されたら
  23. セルAB1の1行下、9列右(AK2)に”女”を入力
  24. Ifステートメント終了
  25. セルAB1の1行下が空欄でなかったら
  26. セルAB1の最終行の1行下にテキストボックス1の値を入力
  27. セルAC1の最終行の1行下にテキストボックス2の値を入力
  28. セルAD1の最終行の1行下にテキストボックス3の値を入力
  29. セルAE1の最終行の1行下にテキストボックス4の値を入力
  30. セルAF1の最終行の1行下にテキストボックス5の値を入力
  31. セルAG1の最終行の1行下にテキストボックス6の値を入力
  32. セルAH1の最終行の1行下にテキストボックス7の値を入力
  33. セルAI1の最終行の1行下にテキストボックス8の値を入力
  34. セルAM1の最終行の1行下にラベル28(更新状況)の表示文字を入力
  35. セルAJ1の最終行の1行下にセルP2(更新日)の値を入力
  36. もしオプションボタン4が選択されたら
  37. セルAK1の最終行の1行下に”男”を入力
  38. そうではなくもしオプションボタン5が選択されたら
  39. セルAK1の最終行の1行下に”女”を入力
  40. Ifステートメント終了
  41. Ifステートメント終了
  42. 整数型の変数iを宣言
  43. 変数iは「1」とする
  44. セルAB列のi+1行目に空欄以外になったら(値が入力されたら)
  45. セルAA列のi+1行目に変数iを入力
  46. 変数iはi+1で入力する(連番)
  47. 繰り返す
  48. Ifステートメント終了
  49. Ifステートメント終了
  50. ユーザーフォーム1を閉じる
  51. 画面更新処理を再開する
  52. マクロ終了

だいぶ長いVBAコードになりましたが、これで住所録の新規登録で最終行を見つけて自動的に入力できるようになって、活用できると作業効率化できますよ。