こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

最終行/処理対象のデータまでを表すVBA

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

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

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

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

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

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

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

投稿日時 - 2017-09-28 21:41:17

QNo.9379859

困ってます

質問者が選んだベストアンサー

(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]という
4つのセル範囲オブジェクトをそれぞれの行として捉えたコレクションです。
 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
のように、連続領域の
 先頭行のインデックス、と、行数、を加えて、1引く
というやり方で求められます。
 先頭行のインデックス、行数、最下行のインデックス、
3つの値を求めたい場合などでは、後者のやり方になります。
 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
が2回、コールされます。
Worksheet_SelectionChange プロシージャの書き方次第では、
トラブルの元になるということも、
一応知っておいてください。
これこそ、確認を怠って他人に薦めてはいけない、
という風に私は思っています。
まぁ回答者としては、
質疑が重くなるし、本題とそれたことを書くのも気が退けるので、
他に方法が無い場合にだけ注釈付で使うのが、
SpecialCells(xlLastCell)だったりします。
自分で書く分には、すべてを掌握している訳ですから、
気を抜いていても問題になることは少ないですけれど、
このことを知らないでいると、
後になってWorksheet_SelectionChangeを追加する時に、
原因不明のトラブルに戸惑うこともあるようです。

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

投稿日時 - 2017-09-29 07:25:41

お礼

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

投稿日時 - 2017-10-01 22:22:01

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.3

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

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

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

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

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

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

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

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

投稿日時 - 2017-09-29 07:22:59

ANo.2

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

投稿日時 - 2017-09-28 23:00:04

お礼

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

投稿日時 - 2017-10-01 22:11:46

ANo.1

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

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

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

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

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

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

投稿日時 - 2017-09-28 22:48:12

お礼

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

投稿日時 - 2017-10-01 22:09:21

あなたにオススメの質問