ExcelマクロVBA技術解説 | Rangeの使い方:最終行まで選択を例に | ExcelマクロVBAの問題点と解決策、エクセルVBAの技術的解説



最終更新日:2017-12-01

Rangeの使い方:最終行まで選択を例に


Rangeの使い方・書き方について、データ最終行まで選択する場合を例に説明します、
Rangeの書き方なので、RangeオブジェクトではなくRangeプロパティの解説という事になります。

最近続けざまに、以下のようなコードを見かけました。

Range("A2", Range("A2").End(xlDown)).Select
Range("A2", Cells(Rows.Count, 1).End(xlUp)).Select

Selectの部分は、Copyだったりでしたが、基本的な書き方としては同じです。

しかも、これらが、VBA逆引き辞典からの引用だったり、ネットから探したとの事。
ちょっと信じられない感じです。
指定方法が分からずに、試行錯誤でこの記述にたどり着いたのなら、それはその人をむしろ誉めたいくらいですが、
書籍や、ネットで情報発信する人が書くべきコードではないと思います。
以下、順に説明しますが、
基本的なRangeの指定方法については、
ExcelマクロVBA入門第9回.Rangeでのセルの指定方法
こちらを参照してください。

A1セルに見出しがあり、A2からA10にデータが入っているとします。
そこで、A2〜A10を選択するVBAでのRangeの書き方のいろいろです。
ここでは動作確認しやすいようにSelectで説明しますが、
実際には、Copyメソッド等になることが多いと思います。

まずは、固定位置として書くと、
Range("A2:A10").Select
これは極一般的な書き方なので問題ないでしょう。

少し違う指定方法として
Range("A2", "A10").Select
これでも同じ指定となります。

固定位置としてではなく、データの最終行を取得して指定する場合ですと、
Range("A2", Range("A1").End(xlDown)).Select
これが最初に書いたもので、どうしてこんな書き方になってしまうのか・・・
これは悪い例です。

そもそも途中にブランクのセルがあると、最終行が正しく取得できません。
まあ、それで相談をうけたのですけど。
それでは、これもたまに見かける書き方ですが、
Range("A2", Range("A65536").End(xlUp)).Select
これ、2003までなら良いですけど、2007が出てから久しいですし、今や2013ですから、
固定の65536ってわけにはいきません。
では、1048576とするかって事ですが、これだと互換モード(.xls)では使えません。

最終行は正しく取得しましょう。
Range("A2", Cells(Rows.Count, 1).End(xlUp)).Select
こういう事になりますね。

しかし、この指定、見ていてなんとなく違和感というか、変な感じがしませんか、私だけかなw
これは、
Range(セル番地文字, Rangeオブジェクト)
このような指定になっているのです。

Rangeプロパティのヘルプを見てみましょう。
WorksheetのRangeプロパティのヘルプより。
セルまたはセル範囲を表す Range オブジェクトを返します。
構文
式.Range(Cell1, Cell2)
式 Worksheet オブジェクトを表す変数。
パラメーター
名前 必須/オプション データ型 説明
Cell1 必須 バリアント型 (Variant) セル範囲の名前を指定します。これは、コード記述時の言語の A1 形式での範囲である必要があります。
範囲名には、範囲を表す演算子 (:)、共通部分を表す演算子 (スペース) または複数の範囲を表す演算子 (,) を含めることができます。また、ドル記号 ($) は含めることはできますが、無視されます。
範囲の一部にローカルに定義した名前を使用できます。名前を使用する場合、その名前はコード記述時の言語と見なされます。
Cell2 オプション バリアント型 (Variant) セル範囲の左上隅と右下隅のセルを指定します。
各引数には、単一のセル、列全体、または行全体を含む Range オブジェクト、あるいはコード記述時の言語で単一のセルの名前を示す文字列を指定できます。

備考
オブジェクト修飾子を指定せずにこのプロパティを使用すると、ActiveSheet.Range のショートカットとなります。つまり、アクティブ シートから範囲を取得します。アクティブ シートがワークシートでない場合、このプロパティは失敗します。
Range オブジェクトに対して使用すると、このプロパティは、その Range オブジェクトと相対的に動作します。たとえば、セル C3 を選択すると、Selection.Range("B1") は、Selection プロパティで取得される Range オブジェクトと相対的な範囲を取得するので、セル D3 が返されます。一方、コード ActiveSheet.Range("B1") は必ずセル B1 を取得します。

使用例
次の使用例は、シート 1 のセル A1 の値を 3.14159 に設定します。
Worksheets("Sheet1").Range("A1").Value = 3.14159

次の使用例は、シート 1 のセル A1 に数式を作成します。
Worksheets("Sheet1").Range("A1").Formula = "=10*RAND()"

次の使用例は、シート 1 のセル範囲 A1:D10 に対してループを行います。セルの値が 0.001 未満の場合は、値をゼロ (0) に置き換えます。
For Each c in Worksheets("Sheet1").Range("A1:D10")
  If c.Value < .001 Then
    c.Value = 0
  End If
Next c

次の使用例は、"TestRange" という名前の範囲に対してループを行い、範囲内の空のセル数を表示します。
numBlanks = 0
For Each c In Range("TestRange")
  If c.Value = "" Then
    numBlanks = numBlanks + 1
  End If
Next c
MsgBox "There are " & numBlanks & " empty cells in this range"

次の使用例は、シート 1 のセル範囲 A1:C5 のフォント スタイルを斜体に設定します。Range プロパティの構文 2 を使用します。
Worksheets("Sheet1").Range(Cells(1, 1), Cells(5, 3)).Font.Italic = True

※若干の編集を加えています。

先の使い方は、このヘルプが原因なのでしょう。
Range(Cell1, Cell2)
このCell1の説明が、「セル範囲の名前を指定します。」となっていますからね。
しかし、これはヘルプを書いた人の意図と少し違っているものと思われます。
最後の、「Range プロパティの構文 2 を使用」ここに注目してください。

つまり、構文1と構文2があり、
構文1は、
Range(Cell1)
Cell1は、セル範囲の名前を指定
構文2は、
Range(Cell1, Cell2)
Cell1とCell2は、「セル範囲の左上隅と右下隅のセルを指定
と考えるべきなのです。

理由として、
Range(Cells(1, 1))
このような記述が出来ないことにあります。
Range(Cells(1, 1), Cells(5, 3))、これができるのに、Range(Cells(1, 1))、こちらができないのです。
そして、そもそも、
Range(Cells(1, 1), Cells(5, 3))
この指定は、ヘルプのCell1の説明に反しています。
「セル範囲の名前を指定します。」と書いてあるのに、違う指定となっています。
この理由により、Rangeプロパティの作者の意図は、
構文1と構文2を明確に区分けしているという事です。

ただし、
構文2のCell指定は、バリアント型で、
「Range オブジェクト、あるいはコード記述時の言語で単一のセルの名前を示す文字列を指定」
となっていますので、先のような文字列とオブジェクトの混在も許されているということです。
ただし、構文2で文字列を指定するという事は、その文字列のRangeオブジェクトへの変換を、
Rangeプロパティにやってもらっているだけのことでしかありません。
ですから、先の書き方が絶対にダメだと言っているわけではなく、
あまり良い書き方ではないという事、初心者に伝えるべき書き方ではないということです。
このような書き方を見せられても、初心者は応用が効かなくなってしまいます。
そして、先のコードも、そのような問い合わせを受けたものです。

では、構文1と構文2での書き方になります。
構文1
Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).Select
こうなっちゃいますよね、これは構文1に忠実に書いてはいますが、このような書き方は止めましょう。
その為に、構文2が用意されているのですから。
構文2
Range(Range("A2"), Cells(Rows.Count, 1).End(xlUp)).Select
これが推奨の書き方になります。
もちろん、Range("A2")は、Cells(2, 1)のどちらでもよいです。

正直、分かっている人にとっては、そんなにこだわるほどの問題ではないのですが、
VBAを覚えようとしている人に見せるコードとしては、
やはり基本に忠実なコードを提示すべきだと思い、かなり気にかかったのでここに書きました。


RangeとCellsの関連記事





同じテーマ「マクロVBA技術解説」の記事

Rangeオブジェクト.Valueの省略について
ユーザー操作を制限する(Locked,Protect,ScrollArea)
シートに数式を設定する時のセル参照の指定方法
標準モジュールとシートモジュールの違い
オートフィルタ(AutoFilter)の使い方まとめ
複雑な条件(複数除外等)のオートフィルター(AutoFilter)
クリップボードを使わないセルのCopy
フルパスをディレクトリ、ファイル名、拡張子に分ける
Colorプロパティの設定値一覧
VBAを定型文で覚えよう

新着記事 ・・・新着記事一覧を見る

VBAを定型文で覚えよう|ExcelマクロVBA技術解説(3月26日)
VBAスタンダード試験対策まとめ|MOS VBAエキスパート対策(3月16日)
ユーザーフォームとメニューの操作|MOS VBAエキスパート対策(3月14日)
ファイルの操作|MOS VBAエキスパート対策(3月14日)
ユーザーフォームの各種イベント|Excelユーザーフォーム(3月13日)
レジストリの操作|MOS VBAエキスパート対策(3月12日)
変数と配列|MOS VBAエキスパート対策(3月12日)
Colorプロパティの設定値一覧|VBA技術解説(3月12日)
APIとOLEオートメーション|MOS VBAエキスパート対策(3月11日)
エラーへの対処|MOS VBAエキスパート対策(3月10日)

アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|ExcelマクロVBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数とデータ型(Dim)|ExcelマクロVBA入門
6.ひらがな⇔カタカナの変換|エクセル基本操作
7.マクロって何?VBAって何?|ExcelマクロVBA入門
8.定数と型宣言文字(Const)|ExcelマクロVBA入門
9.とにかく書いて見よう(Sub,End Sub)|VBA入門
10.繰り返し処理(For Next)|ExcelマクロVBA入門



  • >
  • >
  • >
  • Rangeの使い方:最終行まで選択を例に

  • このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


    記述には細心の注意をしたつもりですが、
    間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
    なお、掲載のVBAコードは自己責任で使ってください。万一データ破損等の損害が発生しても責任は負いません。





    このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

    本文下部へ

    ↑ PAGE TOP