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

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

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

波乗りアヒル

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

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

通常B1セルの値を、ユーザーフォーム１に配置したテキストボックス１に表示する時の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列の入力済み最終行のセルの値をテキストボックス１へ、右隣のセル（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（A列）の入力済み最終行を選択する
ユーザーフォーム1のテキストボックス1の値は選択されたセルの値にする
ユーザーフォーム2のテキストボックス2の値は選択されたセルの1つ右のセルの値にする
マクロ終了

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

もしテキストボックスの数がたくさんあっても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（A列）の入力済み最終行を選択する
ユーザーフォーム1のテキストボックス1の値は選択されたセルの値にする
ユーザーフォーム2のテキストボックス2の値は選択されたセルの1つ右のセルの値にする
繰り返し処理「変数i」を宣言して3～8まで行う
ユーザーフォーム1に配置したテキストボックス「i」（3～8という意味）
表示は選択されたセルの「i」+3の値（右に6列目～11列目）のセル値にする
Withステートメント終了
繰り返し処理をする
画面のちらつき防止再開
マクロ終了

波乗りアヒル

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

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

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

エクセルVBAでセルを選んでも画面は動かないようにする方法

ユーザーフォームに配置したコマンドボタンを押すと様々な作業を自動で行ってくれるVBA。とっても便利で仕事の作業効率化ができるのですが、押すたびに画面が切り替わっては、目障りですよね。たった1行加えるだけでちらつき防止できるのでぜひマネして取り入れてみてはいかがですか？

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

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

波乗りアヒル

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

新規データを登録すると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行下（1行上という事）のセルを選択する
ユーザーフォーム1のテキストボックス1の値は選択されたセルの値にする
ユーザーフォーム2のテキストボックス2の値は選択されたセルの1つ右のセルの値にする
繰り返し処理「変数i」を宣言して3～8まで行う
ユーザーフォーム1に配置したテキストボックス「i」（3～8という意味）
表示は選択されたセルの「i」+3の値（右に6列目～11列目）のセル値にする
Withステートメント終了
繰り返し処理をする
画面のちらつき防止再開
マクロ終了

入力済み最終行は既に選択されている状態なので、「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で住所録検索してリストを修正するユーザーフォームを作る

住所録リストを作成した後に、特定の人を探したり修正するのって意外と面倒ですよね。探すのが大変なので一発検索・修正・上書きができる修正フォームをエクセルで作ったので、作業効率化できるのでマネして取り入れてみてはいかがですか？

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

波乗りアヒル

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

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

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

Microsoft Officeランキング

ユーザーフォームを使えばこんなに便利に早く作業が終わりますよ

エクセルVBAで住所録入力フォームを作る方法

エクセルで住所録などを入力や修正するのに探すのって面倒ですよね？ユーザーフォームを使えば一瞬で検索・修正ができるのに、使わないなんて損していると思いませんか？

ユーザーフォームでオリジナル入力画面を作る

オリジナルの入力フォームを作ることができるユーザーフォームの作り方をご紹介します。エクセルの学校に行っていないおじさんでもできたので、仕事の作業効率化の為にぜひマネして取り入れてみてはいかがですか？

エクセル関数とVBAで和暦西暦対応カレンダー作成 | とりっぷぼうる

エクセル関数を基本として作成し、ユーザーフォームで日付選択ができるようにすることで複雑なマクロが分からなくても、自由にカスタマイズして自分のエクセルに組み込んでみませんか？住所録など作成する時に、和暦がまだまだ多いので和暦・西暦変換もでき

Excel-VBAもくじ～エクセルの作業時間を半減させる方法

たった1秒で完了！驚異の作業効率へ。Excel-VBAを使うと入力・集計・検索がボタンひとつででき、30分の作業がたった6分に。実例集をまとめてみました。団塊ジュニアのオジサンができるのだから。

波乗りアヒル

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

鉄道・バス（フリーパス・フリーきっぷ・1日乗車券）一覧

フリーパスや鉄道会社・バス会社が発行する企画きっぷなどを活用して、お得に旅を満喫してみませんか？

404: ページが見つかりませんでした | とりっぷぼうる

旅行先での移動に鉄道やバスを使う時にフリーパスや往復乗車券付きクーポンを使ってお得に楽しみたい時、意外と調べるのが面倒なんですよね。通常1人分の料金表示なのにJRのフルムーン夫婦グリーンパスは2人分の料金表示だったり、電話で聞いて分かる事も...