VBAによるEnd(xlDown)によるセルの選択について

Question

前提

以下のようなエクセルの表があった場合に
VBAにてB列目の最初の行から最後の行の文字列を取得し
B列の値をメッセージボックスで出力するようにしております。

<エクセル表の例>

A	B
1	hoge
2	fuga
3	piyo

<VBAコード>

Set r = Range("B1", Range("B1").End(xlDown))
For Each str In r
  MsgBox(str)                              'hoge,fuga,piyoの順に出力される
Next

解決したい内容

上記の表の場合は、特に問題はないですが、
表が以下のようになっている場合に同様の処理を実行すると、B列のxlDownにおいて、
エクセル上での最大行数まで行ってしまうため処理がオーバーフローを起こしてしまいます。

<エクセル表の例>

A	B
1	hoge

上記のような場合は、End(xlDown)を実行せず、Range("B1")のみを出力したいと思うのですが、
何かいい方法はありませんでしょうか。

恐れ入りますが、ご回答いただけますと幸いです。
よろしくおねがいします。

Answer 1

一番下から上に見ていけばいいのでは。

Set r = Range("B1", Range("B" & Rows.Count).End(xlUp))

余談ですが、Strという関数が存在するので、変数に使うのはよろしくないですよ。

Comments

[yutkts]

たしかに言われたとおり下から上で解決できそうです！
Rows.Countで一番下という意味になるんですね。
コメントいただきありがとうございました！

変数strについてもご指摘ありがとうございます。

Answer 2

想定できるあらゆる場合を考慮して仕様を決定して、コーディングすべきです。

質問のようにデータが1件だけの場合はすでに回答が付いていますが、

---

下記のように空欄があったらどうするのか。

A	B
1	hoge
2	fuga
3
4	piyo

hoge, fuga まででいいのか。
piyoも出力するのか。

前者ならkobacさんの回答、後者ならttyp03さんの回答になります。

---

データが0件ならどうするのか。

A	B

何もしないのか、空欄のメッセージを出力するのか、エラーメッセージを出力するのか。

それによってもコードが変わってきます。

Comments

[yutkts]

たしかにご指摘いただいた通り、空白がある場合までは想定しておりませんでしたが
空白があった場合でも、piyoまでを出力対象としたい考えております。

また、データが0件の場合は、処理は行わないものとして、条件分岐で抜けるつもりでした。

ご指摘いただきありがとうございました！

Answer 3

End(xlDown)は実行してしまって、最大行数まで行ってしまったかを確認し、行ってたら分岐する形でどうでしょうか。

If r.Count = Rows.Count Then
  MsgBox Range("B1")
  Exit Sub
End If

他にも「B2セルを確認して空白ならEnd(xlDown)しない」とか色々やり方はあると思います。

Comments

[yutkts]

こちらの条件分岐でもうまくいくのを確認できました。
コメントいただきありがとうございました！