入力済み最終行セルを探して同じ行の複数セルの値を一括取得する方法
エクセルで指定したセルの右隣のセルの値を、ユーザーフォームに配置したテキストボックスに転記出来れば便利だと思いませんか?
更に見つけたセルの1行上や下の同じ列の値をテキストボックスに、コマンドボタンを押すたびに取得できたら、もっと便利だと思いませんか?
住所録や商品リストであったら便利なので、そんなコマンドボタン作ってみました。
例えば、当たり前ですけどA1セルの右隣といえばB1セルになります。
通常B1セルの値を、ユーザーフォーム1に配置したテキストボックス1に表示する時のVBAコードはこのようになりますよね。
UserForm1.TextBox1.Text = Range(“B1”).Value
End Sub
または
UserForm1.TextBox1.Text = Cells(1,2).Value
End Sub
または
UserForm1.TextBox1.Text = Range(“A1”).Offset(0, 1).Value
End Sub
3つどの方法でもB1セルの値をテキストボックスに表示できる
上記3つのようにセルの場所が決まっていれば簡単にVBAコードを書くことができますが、セルの場所が固定できない場合、入力済み最終行はどうしますか?
スポンサーリンク
入力済み最終行と同じ行のセルの値を複数取得
住所録や商品台帳などでは、登録するたびにリスト最終行の行番号はどんどん増えていきますので、行番号を指定することは困難です。
入力済み最終行を自動選択させよう!
A列の入力済み最終行のセルを選択するVBAコードはこのようになります。
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へそれぞれ表示させましょう。
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つ右のセルの値にする
- マクロ終了
これで、テキストボックス1と2にそれぞれ入力済み最終行のセルの値が表示されます。
もしテキストボックスの数がたくさんあってもActiveCell.Offset(0, 1).Valueの1を2に代えれば2つ隣になるので、続けてVBAコードを書き続けても問題はなくできます。
面倒なので繰り返し処理で一括表示しよう!
指定した回数分繰り返し処理を行ってくれるFor~Nextを組み合わせますが、For~Nextの詳細はここでは省略します。
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行目の『画面ちらつき処理』も追加しています。
この追加した処理を入れておかないと、コマンドボタン押すたびに選択したセルの場所へ画面が行ったり来たりして、目障りなのとマクロ処理中に処理が速くなるのでいつも入れるようにしています。
これで入力済み最終行の同じ行の複数のセルの値を、テキストボックスに表示できるようになったので、ついでに「ひとつ上の行」や「ひとつ下の行」もボタンひとつで同じように表示できるようになったら、便利ですよね。
【戻る】【次へ】ボタンを作る
一般的には【戻る】【次へ】という名称かな?
新規データを登録すると1行下へ追記されるので、1行上のデータを表示させるには、ひとつ前に入力したデータということになりますよね。
そこで画像では【ひとつ前】ボタンとして、サンプルを作りました。
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行は全て一緒なので簡単ですね。
では次に、【ひとつ後】ボタンを設定しましょう。
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行目以降は全く一緒です。
更に、テキストボックスに表示されたデータを修正して、書き換えたデータをセルに戻すこともできると便利ですよね。
このまま書き続けると、住所録でのデータ修正と同じことを書くことになるので詳細は省略します。
この他にも最終行に関して活用できると作業効率化できますよ。
- 入力済み最終行を取得する
- 入力済み最終行の下に新しく入力する
- 入力済み最終行を探して同じ行の複数セルの値を一括取得する
- 入力済み最終行まで同じ値のセルを結合・解除する
- 入力済み最終行まで不要な行を非表示にする
ご覧いただきありがとうございます。
ブログランキングに登録しています。
少しでもお役に立てれば幸いで、参考になったようでしたら応援よろしくお願いします。
エクセルの勉強お疲れ様です。ちょっとひと休みしませんか?