最終行／処理対象のデータまでを表すＶＢＡ

質問

こんばんは、データの最終行／処理する対象のセルまで処理する場合のＶＢＡの記述について質問させてください！ＶＢＡの最終行／処理する対象のセルまで選択するために使用する記述方法が何種類かありますが、そのうち、書籍やネットで検索しても出てこない記述方法について今一つ理解ができていません(T_T)

本屋で売っている書籍やネットで検索すると出てくる記述方法
Range～.End(xlUp).Row
Range～.CurrentRegion
Range～.SpecialCells(xlLastCell)
Range～.Cells(Rows.Count, 1).End(xlUp).Row

上記のようなＶＢＡは書籍やネットで検索すると説明や違いについても出てくるので、説明を読めば理解ができるのですが、
Sheets("テスト").Range("A1:C" & Sheets("テスト").Cells　_(1).CurrentRegion.Rows.Count)

Range("A1").CurrentRegion.Cells(Range _("A1").CurrentRegion.Cells.Count).Row

上記のようなＶＢＡについては検索しても説明など出てこないうえに、
なぜ上記のような記述になるのかあまり理解できていません(>_<)

書籍やネットで検索すると出てくる「Range～.CurrentRegion」などの記述方法から何となく、CurrentRegionやCells.Countを使わないといけないというのは理解できますが、なぜ「～CurrentRegion.Rows.Count)」はRowsがカッコの中に入っていて、「～CurrentRegion.Cells.Count).Row」はカッコの外に出ているのか？？といった細かいことが分からずにいます。。。

半ば丸暗記のようにして使ってしまっているのですが、もし上記のようなＶＢＡについてわかるかたがいらっしゃれば、ご教授いただけるととても嬉しいです！
また、上記以外にもまだ検索しても出てこないようなＶＢＡがあるのでしょうか？？
頭が混乱しそうです！(゜Д゜;)

回答No.4

real beatin（@realbeatin） · ベストアンサー

（No.3の続き）
さて、主題であるCurrentRegionの扱いについてですが、
Excelの用語としては、「アクティブ セル領域」と表現されることが多いですが、
VBAでは、range.CurrentRegion
「指定のセルを含み、空白の行と列で囲まれているデータ領域（連続領域）」
といった表現になるのでしょうか。丸めると、
「指定のセルを含む一連なりの表全体の範囲」という言い方も出来そうです。
　ひとつのシートに複数の表があるかもしれないけれど
　一応、ひとつのテーブルと看做して問題ない
という、前提条件、でこそ意味を持つのが、CurrentRegionです。
　　　A　　B　　　C
　1　[id] [Name] [Mail]
　2　id1　uName1　Url1
　3　id2　uName2　Url2
　4　id3　uName3　Url3
のような表について、VBAで、この表全体を取得するには、
　Cells.CurrentRegion
　Cells(1).CurrentRegion
　Range("A1").CurrentRegion
　Range("B2").CurrentRegion
　Cells(4, 3).CurrentRegion
表の中にあるセルであれば、どのセルを指定しても、
「一連なりの表全体の範囲」として、
Range("A1:C4")を取得できます。

> Range("A1").CurrentRegion.Cells(Range("A1").CurrentRegion.Cells.Count).Row
Cellsは省略可能で
　Range("A1").CurrentRegion(Range("A1").CurrentRegion.Count).Row
と書くことも出来ますが、
先に挙げたシートサンプルで言えば、
　Range("A1:C4")(Range("A1:C4").Count).Row
と、この場合同じ意味になります。
Range("A1:C4").Countは4*3で12ですので、
　Range("A1:C4")(12).Row
に相当します。
[A1:C4]というセル範囲の、'12'番めのセルの、'行インデックス'
と翻訳したとして、疑問に残りそうなポイントは、
「'12'番め」でしょうか。
指定した範囲[A1:C4]の
　先頭（左上）セルからまず右方向に数え始めて、
　　終端列まで数えたら、次の行の一番左のセルを数える
という順番になります。
　A1-Range("A1:C4")(1)　B1-...(2)　C1-...(3)
　A2-...(4)　　　　　　　B2-...(5)　C2-...(6)
　A3-...(7)　　　　　　　B3-...(8)　C3-...(9)
　A4-...(10)　　　　　　B4-...(11)　C4-Range("A1:C4")(12)
Range("A1:C4")(12)は[C4]です。
Range("A1:C4")(Range("A1:C4").Count)は領域の最後のセルを指し[C4]です。
Range("A1:C4")(Range("A1:C4").Count).Row
　は、領域の最後のセルの行インデックス=4です。
Range("A1").CurrentRegion(Range("A1").CurrentRegion.Count).Row
　と書いても、この例では、同じ意味になるので、
　連続領域の最後のセルの行インデックス=4です。
総じて、求めようとしているのは
　Range("C4").Row
[C4]の行インデックスとしての、4、です。

> Sheets("テスト").Range("A1:C" & Sheets("テスト").Cells(1).CurrentRegion.Rows.Count)
先に挙げたシートサンプルに照らして、
シートの指定を省略すれば、
　Range("A1:C" & Cells(1).CurrentRegion.Rows.Count)
となります。
　Cells(1).CurrentRegion
は[A1:C4]なので、
　Range("A1:C" & Range("A1:C4").Rows.Count)
と同じ意味です。
　Range("A1:C4").Rows
は、[A1:C1],[A2:C2],[A3:C3],[A4:C4]という
４つのセル範囲オブジェクトをそれぞれの行として捉えたコレクションです。
　Range("A1:C4").Rows.Count
で、Range("A1:C4")の行数を求めると、=4です。
ここで求まった'4'を
　Range("A1:C" & Range("A1:C4").Rows.Count)
の括弧の内側に代入すると、
　Range("A1:C" & 4)　つまり　Range("A1:C4")
ということになります。遡って、
　Range("A1:C" & Cells(1).CurrentRegion.Rows.Count)
　Sheets("テスト").Range("A1:C" & Sheets("テスト").Cells(1).CurrentRegion.Rows.Count)
は、この例では、
　Range("A1:C4")
を指します。
総じて、求めようとしているのは
セル範囲[A1:C4]です。

また、
> Range("A1").CurrentRegion.Cells(Range("A1").CurrentRegion.Cells.Count).Row
は、
　With Range("A1").CurrentRegion
　　returnV = .Cells(.Cells.Count).Row
　End With
のようにも書けるのですが、
場合によっては、
　With Range("A1").CurrentRegion
　　returnV = .Row + .Rows.Count -1
　End With
のように、連続領域の
　先頭行のインデックス、と、行数、を加えて、１引く
というやり方で求められます。
　先頭行のインデックス、行数、最下行のインデックス、
３つの値を求めたい場合などでは、後者のやり方になります。
　With Range("A1").CurrentRegion
　　firstRow = .Row
　　cntRows = .Rows.Count
　　lastRow = firstRow + cntRows -1
　End With
前者のやり方では、最下行のインデックス、しか求められませんから、
【目的】によって書き分ける、ということになります。

ただ、そもそもの、CurrentRegionを使う理由、というのは、
処理したいセル範囲を固定的絶対的に捉えることのできない、
可変で曖昧さの残るような【条件】だから、という点も
忘れないでください。
また、Range("A1").CurrentRegion、Cells(1).CurrentRegion
どちらも、[1:2]行が空、[A:B]列が空、いずれかの【条件】下
では、[A1]を参照するだけで、表全体を捉えることにはならない、
ということも知っておいて下さい。

加えて、[シートの保護]が適用されたシートでは、
CurrentRegionはエラーになるので使えません。
このことも書き分ける【条件】のひとつとして
憶えておいた方がいいでしょう。

ついでに、
　range.SpecialCells(xlLastCell)
について。
当該シートで、
　Worksheet_SelectionChange イベント
を使う場合には、ご注意を。
　range.SpecialCells(xlLastCell)
を一度実行するだけで、
　Worksheet_SelectionChange
が２回、コールされます。
Worksheet_SelectionChange プロシージャの書き方次第では、
トラブルの元になるということも、
一応知っておいてください。
これこそ、確認を怠って他人に薦めてはいけない、
という風に私は思っています。
まぁ回答者としては、
質疑が重くなるし、本題とそれたことを書くのも気が退けるので、
他に方法が無い場合にだけ注釈付で使うのが、
SpecialCells(xlLastCell)だったりします。
自分で書く分には、すべてを掌握している訳ですから、
気を抜いていても問題になることは少ないですけれど、
このことを知らないでいると、
後になってWorksheet_SelectionChangeを追加する時に、
原因不明のトラブルに戸惑うこともあるようです。

実践の中で迷いを減らす意味で整理しますが、
　処理対象のセル範囲
　　そのもの(Range)を取得
　　の先頭行・先頭列のインデックス(数値)
　　のセル範囲の行数・列数(数値)を取得
　　の最下行・最終列のインデックス(数値)を取得
　　のセル数(数値)を取得
など、様々な処理の中で、どれを選択（組合わせ）するのが、
今やりたい処理に最適なのか、とか、
それぞれの処理で求めようとしているのは何なのか、
意識して常に把握できるよう心掛けてください。
基本的で、当たり前のことのようですが、
結構迷子になっている方、見掛けます。
【目的】や【条件】に合わせて書き分けられるように
なるまでは時間掛かると思いますが、急がなくて大丈夫です。

お礼

沢山の情報ありがとうございます！(^－^)Rang("B2").CurrentRegionでA列まで取得できるというのは意外です！今度自分でもテストで試してみようと思います。また、CurrentRegionが保護シートでは使えないとは知らなかったです…！色々と教えていただき、ありがとうございました！

回答No.3

real beatin（@realbeatin）

こんにちは。
（ちょっと長くなります。適当に読み飛ばしてください）

えっと、、、。
書き手の趣向という面も多少はあるかも知れませんが、
【目的】や、【条件】に合わせて書き分けて使う、
というのが本筋で、こういった課題に疑問を持つことは、
VBAを学ぶ上で、とても正当なものですし、
こういう質問をできる、ということだけでも、
未来は明るい、とさえ私は思います。

まずは、基礎的な話からですが、、、。
　Excelにおける「表」
と、
　「テーブル」（Excelでいうテーブルではなく一般的なもの）
に纏わる概論から。

Excelは、本来、表計算アプリケーションです。
作表して、集計して、検索したり、抽出したり、
といったことが主な役割、ということになります。
（つまり、表を扱う為に在るのがのがExcelです。）
これに対して、
データベースアプリケーション（例えばAccess）
では、似たような用途に用いられますが、
データの扱いの基礎になるのは、テーブル、です。
テーブルは、非常に明瞭（シンプル）なもので、
フィールド（項目）（Excelでいえば列）と
レコード（１件ごとのデータ群）（Excelでいえば行）
という二つの概念（無論データもあるけど）しかありません。
Excelでは可能なこととして
ひとつ表の　下や　右に　他の表がある、
というようなことは、テーブルにはあり得ません。
（テーブルとは、ひとつの、表、みたいなものです）
（Excelではひとつのシートに複数の表を表現できます）
加えて、表には空の行があるかも知れないけれど、
テーブルには、空のレコード（Excelでいえば空行）
が挟まれて存在するようなことはあり得ません。
（意味のあるレコードを管理するのがテーブルだから）
（Excelでは未入力であっても１件として扱うことが可能です）
２行で１件というようなレイアウトでデータ扱うようなこと、
Excelでは可能ですが、
テーブルは、そもそも視覚的な行という概念がないですし、
１件のデータ群を一塊りで扱うことしか出来ませんし、
こうした扱い方を意図して作られたものなのです。

テーブルは明瞭で単純ですから、データベースの方が、
効率的かつ高速に各種の求める答えを返してくれますが、
比較すれば、Excelは、
Access同等のデータベースっぽい扱いも可能にしつつ、
様々な用途に（画面で複数のテーブルを扱える等に）
応えられるよう、多様性を持たせたものなのです。

VBA、は、明瞭な命令を与えればこそ、期待通りの結果を
返してくれます。
Excel VBAからすれば、
テーブルの要件を満たした表やシートこそ
扱い易く親和性が高いと言えます。
Excel VBAでシート上のデータを処理しようとする場合、
注目すべきは、
　空行の有無
　ひとつのシートにある表がひとつであるか
　１件のデータの塊りが１行になっているか
概ね、この３点に絞られます。
テーブルとしての条件に近いほど、
VBAでは扱い易い、ということです。

不調に終わるVBA質問として良く見かけるのは、大抵、
これらの条件の適否を伝えられていない、ケースです。
また、何も表現されていない場合の回答者の対応は、大抵、
A1から始まる、空行・空列を挟まない連続したひとつの表、
で、１件１行のデータの塊りを扱うもの、という暗黙の前提
に頼る、というのがほぼ常識のようになっています。
常識というのは、大概、歪なもので、何故か、
　ひとつのシートに複数の表がある場合
　表の中に空行がある場合
にだけは、対応できるように、わざわざ、
　Cells(Rows.Count, 1).End(xlUp).Row
というような、実態を無視した、回答者によるご都合主義的な
余計なお節介、が、いつの間にかQA掲示板では
重用されるようになりました。
テーブルとしての要件
（少なくとも１件以上のレコードがある、
　１行めは項目名、という要件も加えて）
を満たしているならば、
わざわざ遠回りして、下から上に最終行を探す必要などないのです。
（本来は上から下　Cells(1, 1).End(xlDown).Row）
実務上の常識と、ＱＡ掲示板に見られる回答場面（初心者向け解説分）
での常識とには、少なからず、乖離があります。
殆どの場合で、この乖離の原因は、確認の為にわざわざ
訊き返してその返答を待つのが億劫だから、ということに尽きます。
残念ながら、、、。
さておき、

シートのレイアウト次第で、最終行の求め方は変わる、どうして？
と考える上での前提を以上、書きました。
テーブルとしての要件を満たすような作表がされていれば、
最終行の求め方は、Cells(1, 1).End(xlDown).Row で十分ですし、
他の書き方でも通用します。
逆の考えると、VBAを扱うなら、シートデザインにも
気を配っておいた方がいい、ですね。
（次の投稿に続きます）

回答No.2

imogasi

質問のようなコードは、応用編でもあり、個人のコードの書き方の癖でもあるので、ＷＥＢで照会しても、出てくるかどうかわかりません。
当たり前のやり方なので、疑問に思うのが、極くＶＢＡの初心者らしいと思える。
引数になっている部分を取り出して、部分的に独立したコード行に変えて
Ｍｓｇｂｏｘで表示させて、様子を考えてみることを勧めます。
とりあえずは、意味を分かって、この流儀を真似すれば、レパートリ―が拡がる。
Sub test2()
x = Sheets("テスト").Cells(1).CurrentRegion.Rows.Count
MsgBox x
End Sub
この場合はCells(1)の部分は」Ａ１セルを指します。
ーー
Sub test01()
Sheets("テスト").Range("A1:C" & Sheets("テスト").Cells(1).CurrentRegion.Rows.Count).Select
End Sub
は　Range("A1:C〇")の〇の部分は数値の文字ですが、その値を求めるコードを代入し、一体化させた表現です。
ほとんどどの言語でも、こういう書き方は許されていると思います。
しかし、私の経験では、すべての場合というわけではないと思うので、１つ１つ慣れるほかないと思います。
エクセルの関数式などでは、少し複雑な式（課題解決の式）はこんなのばかりです。
Sub test03()
x = "2"
Worksheets("Sheet" & x).Select
End Sub
はＯＫでした。使えないケースを、今は思い付かないが。

お礼

ほんとですね…！MsgBoxを活用して、自分でも上記のＶＢＡを分解して取得範囲の違いについて調べてみたらよいのですね！(>_<)ありがとうございます、道のりは遠いですが頑張ります…！

回答No.1

FEX2053

CurrentRegionは、「そのセルがある周囲で、自動的に"範囲"
として認識できる範囲の全部」を意味します。ですので、
CurrentRegion.Rowsで「範囲内の行数」、.Countで「その数」
を数えているんですね。

逆に、Cells.Countは、「選択した範囲内にあるセル」を意味
し、.Rowでその「行」を意味します。要は「求めるべき数字を
調べるときの求め方が微妙に違う」んです。

でも、CurrentRegionがどちらも入っているので、これらの
処理は「自動的に"範囲"として認識できる」ことが前提です。

一方、End(xlUp）は、「物理的に[Ctr]+[↑]でカーソルを動かし
た先」を意味します。こちらは、「自動的に上手く"範囲"と
して認識してくれない」場合でも動作します。

ですので、元々データベースとして設計されたシートなら、
CurrentRegionを使った方が処理が速くわかりやすいですが、
そうでない場合は、End()を使った方が確実です。

ExcelVBAは通常、「.」で「〇〇の」という意味があります。
コード全体ではなく、「.」で一つ一つ区分して理解した
ほうが、理解が進むと思いますよ。

お礼

CurrentRegionとEndプロパティについてご教授いただき、ありがとうございました！色々なかたの回答を見れて、とても参考になりました(^o^)