入力済み最終行セルを探して同じ行の複数セルの値を一括取得する方法

エクセルVBAセルの操作
スポンサーリンク

エクセルで指定したセルの右隣のセルの値を、ユーザーフォームに配置したテキストボックスに転記出来れば便利だと思いませんか?

更に見つけたセルの1行上や下の同じ列の値をテキストボックスに、コマンドボタンを押すたびに取得できたら、もっと便利だと思いませんか?

波乗りアヒル
波乗りアヒル

住所録や商品リストであったら便利なので、そんなコマンドボタン作ってみました。

商品修正フォーム

例えば、当たり前ですけどA1セルの右隣といえばB1セルになります。

通常B1セルの値を、ユーザーフォーム1に配置したテキストボックス1に表示する時のVBAコードはこのようになりますよね。

Private Sub CommandButton1_Click()
UserForm1.TextBox1.Text = Range(“B1”).Value
End Sub

または

Private Sub CommandButton1_Click()
UserForm1.TextBox1.Text = Cells(1,2).Value
End Sub

または

Private Sub CommandButton1_Click()
UserForm1.TextBox1.Text = Range(“A1”).Offset(0, 1).Value
End Sub
波乗りアヒル
波乗りアヒル

3つどの方法でもB1セルの値をテキストボックスに表示できる

上記3つのようにセルの場所が決まっていれば簡単にVBAコードを書くことができますが、セルの場所が固定できない場合、入力済み最終行はどうしますか?
スポンサーリンク


目次

入力済み最終行と同じ行のセルの値を複数取得

住所録や商品台帳などでは、登録するたびにリスト最終行の行番号はどんどん増えていきますので、行番号を指定することは困難です。

波乗りアヒル
波乗りアヒル

入力済み最終行を自動選択させよう!

A列の入力済み最終行のセルを選択するVBAコードはこのようになります。

Private Sub CommandButton1_Click()
Cells(Rows.Count, 1).End(xlUp).Select
End Sub

ちなみにCells(Rows.Count, 1)がA列を指定しているので、Cells(Rows.Count, 2)とすれば2列目のB列を指定することができます。

波乗りアヒル
波乗りアヒル

ここから本番!複数のテキストボックスに表示させる

入力済み最終行のセルは先ほどのVBAコードで見つけられるようになりましたので、今度は同じ行のセルの値をテキストボックスに表示させましょう。

まずはシンプルにA列の入力済み最終行のセルの値をテキストボックス1へ、右隣のセル(B列)の値をテキストボックス2へそれぞれ表示させましょう。

Private Sub CommandButton1_Click()
Cells(Rows.Count, 1).End(xlUp).Select
UserForm1.TextBox1.Text = ActiveCell.Value
UserForm1.TextBox2.Text = ActiveCell.Offset(0, 1).Value
End Sub
  1. コマンドボタン1を押したら
  2. 列番号1(A列)の入力済み最終行を選択する
  3. ユーザーフォーム1のテキストボックス1の値は選択されたセルの値にする
  4. ユーザーフォーム2のテキストボックス2の値は選択されたセルの1つ右のセルの値にする
  5. マクロ終了

これで、テキストボックス1と2にそれぞれ入力済み最終行のセルの値が表示されます。

もしテキストボックスの数がたくさんあってもActiveCell.Offset(0, 1).Valueの1を2に代えれば2つ隣になるので、続けてVBAコードを書き続けても問題はなくできます。

波乗りアヒル
波乗りアヒル

面倒なので繰り返し処理で一括表示しよう!

指定した回数分繰り返し処理を行ってくれるFor~Nextを組み合わせますが、For~Nextの詳細はここでは省略します。

あわせて読みたい
複数のセルの値を別の複数のセルに繰り返し転記する エクセルからエクセルへの差し込み印刷でも使っているのですが、住所録からお礼状などを作成する時に、名前・郵便番号・住所などを顧客リスト全員分を順番に印刷する時...
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Cells(Rows.Count, 1).End(xlUp).Select
UserForm1.TextBox1.Text = ActiveCell.Value
UserForm1.TextBox2.Text = ActiveCell.Offset(0, 1).Value
For i = 3 To 8
With UserForm1.Controls(“TextBox” & i)
.Text = ActiveCell.Offset(0, i + 3).Value
End With
Next i
Application.ScreenUpdating = True
End Sub
  1. コマンドボタン1を押したら
  2. 画面のちらつきを停止する
  3. 列番号1(A列)の入力済み最終行を選択する
  4. ユーザーフォーム1のテキストボックス1の値は選択されたセルの値にする
  5. ユーザーフォーム2のテキストボックス2の値は選択されたセルの1つ右のセルの値にする
  6. 繰り返し処理「変数i」を宣言して3~8まで行う
  7. ユーザーフォーム1に配置したテキストボックス「i」(3~8という意味)
  8. 表示は選択されたセルの「i」+3の値(右に6列目~11列目)のセル値にする
  9. Withステートメント終了
  10. 繰り返し処理をする
  11. 画面のちらつき防止再開
  12. マクロ終了
波乗りアヒル
波乗りアヒル

これで完成で、今のところこのVBAコードでトラブルなく使っています。

2行目と11行目の『画面ちらつき処理』も追加しています。

この追加した処理を入れておかないと、コマンドボタン押すたびに選択したセルの場所へ画面が行ったり来たりして、目障りなのとマクロ処理中に処理が速くなるのでいつも入れるようにしています。

あわせて読みたい
エクセルが重くなるVBAの処理を高速化して画面ちらつきを防ぐ方法 コマンドボタンをクリックして作業を自動化するとVBAコードを読み取りながら処理をしているので、画面がチョコチョコ移動したり切り替わったり目障りだと思ったことあり...

これで入力済み最終行の同じ行の複数のセルの値を、テキストボックスに表示できるようになったので、ついでに「ひとつ上の行」や「ひとつ下の行」もボタンひとつで同じように表示できるようになったら、便利ですよね。

【戻る】【次へ】ボタンを作る

商品修正フォーム

波乗りアヒル
波乗りアヒル

一般的には【戻る】【次へ】という名称かな?

新規データを登録すると1行下へ追記されるので、1行上のデータを表示させるには、ひとつ前に入力したデータということになりますよね。

そこで画像では【ひとつ前】ボタンとして、サンプルを作りました。

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
ActiveCell.Offset(-1, 0).Select
UserForm2.TextBox1.Text = ActiveCell.Value
UserForm2.TextBox2.Text = ActiveCell.Offset(0, 1).Value
For i = 3 To 8
With UserForm2.Controls(“TextBox” & i)
.Text = ActiveCell.Offset(0, i + 3).Value
End With
Next i
Application.ScreenUpdating = True
End Sub
  1. コマンドボタン1を押したら
  2. 画面のちらつきを停止する
  3. 選択されているセルのマイナス1行下(1行上という事)のセルを選択する
  4. ユーザーフォーム1のテキストボックス1の値は選択されたセルの値にする
  5. ユーザーフォーム2のテキストボックス2の値は選択されたセルの1つ右のセルの値にする
  6. 繰り返し処理「変数i」を宣言して3~8まで行う
  7. ユーザーフォーム1に配置したテキストボックス「i」(3~8という意味)
  8. 表示は選択されたセルの「i」+3の値(右に6列目~11列目)のセル値にする
  9. Withステートメント終了
  10. 繰り返し処理をする
  11. 画面のちらつき防止再開
  12. マクロ終了

入力済み最終行は既に選択されている状態なので、「1行上」と指定するだけで1行上の行にある複数のセルの値を表示できるようになるのです。

波乗りアヒル
波乗りアヒル

3行目が違うだけでその他11行は全て一緒なので簡単ですね。

では次に、【ひとつ後】ボタンを設定しましょう。

Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
If ActiveCell = “” Then
MsgBox “データがありません”
ActiveCell.Offset(-1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
UserForm2.TextBox1.Text = ActiveCell.Value
UserForm2.TextBox2.Text = ActiveCell.Offset(0, 1).Value
For i = 3 To 8
With UserForm2.Controls(“TextBox” & i)
.Text = ActiveCell.Offset(0, i + 3).Value
End With
Next i
End If
Application.ScreenUpdating = True
End Sub

3行目~6行目には選択したセルが空欄だった時に、メッセージを出してこれ以上下の行へ行かず、元に戻るようにしています。

7行目はさきほどと違い1行下のセルを選択するので(-1,0)が(1,0)になっています。

8行目以降は全く一緒です。

波乗りアヒル
波乗りアヒル

更に、テキストボックスに表示されたデータを修正して、書き換えたデータをセルに戻すこともできると便利ですよね。

このまま書き続けると、住所録でのデータ修正と同じことを書くことになるので詳細は省略します。

あわせて読みたい
エクセルVBAで住所録検索してリストを修正するユーザーフォームを作る 波乗りアヒル このページは「エクセルVBAで住所録入力フォームを作る」ページの続きとなり、入力データの修正に関するページとなります。 https://tripbowl.com/excel-v...

この他にも最終行に関して活用できると作業効率化できますよ。

波乗りアヒル
波乗りアヒル

ご覧いただきありがとうございます。

ブログランキングに登録しています。

少しでもお役に立てれば幸いで、参考になったようでしたら応援よろしくお願いします。


Microsoft Officeランキング

ユーザーフォームを使えばこんなに便利に早く作業が終わりますよ
あわせて読みたい
エクセルVBAで住所録入力フォームを作る方法 数百人規模の中規模の住所録や顧客データをエクセルで管理している事業所って意外と多いですが、データを検索して修正するのってすごく面倒じゃないですか? 面倒だなあ...
あわせて読みたい
ユーザーフォームでオリジナル入力画面を作る エクセルのデータ入力・検索・修正作業ってとても面倒で時間と手間がかかりませんか? この作業を簡単に素早く終わらせたくないですか? エクセル使うなら今話題の時短...
とりっぷぼうる
エクセル関数とVBAで和暦西暦対応カレンダー作成 | とりっぷぼうる エクセル関数を基本として作成し、ユーザーフォームで日付選択ができるようにすることで複雑なマクロが分からなくても、自由にカスタマイズして自分のエクセルに組み込んで...
あわせて読みたい
Excel-VBAもくじ~エクセルの作業時間を半減させる方法 【コピペOK】このExcel-VBAページに掲載している内容・VBAコードが、少しでもお役に立てるのであればコピーして使っていただいて構いません。 たった1秒で完了!驚異の...
波乗りアヒル
波乗りアヒル

エクセルの勉強お疲れ様です。ちょっとひと休みしませんか?

目次