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



最終更新日:2015-04-16

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を覚えようとしている人に見せるコードとしては、
やはり基本に忠実なコードを提示すべきだと思い、かなり気にかかったのでここに書きました。




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

オブジェクト式について
Dir関数の制限について
画像サイズ(横x縦)の取得について
文字種(ひらがな、全半角カタカナ、半角英大文字等々)の判定
よくあるVBA実行時エラーの解説と対応
ローカルウィンドウの使い方
オブジェクトの探索方法

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

空白セルを正しく判定する方法2|ExcelマクロVBA技術解説(5月6日)
フルパスをディレクトリ、ファイル名、拡張子に分ける|ExcelマクロVBA技術解説(4月15日)
テキストボックスの各種イベント|Excelユーザーフォーム入門(4月9日)
フォルダ(サブフォルダも全て)削除する、Optionでファイルのみ削除|ExcelマクロVBAサンプル集(4月4日)
最後の空白(や指定文字)以降の文字を取り出す|エクセル関数超技(3月26日)
先頭の数値、最後の数値を取り出す|エクセル関数超技(3月26日)
Excelファイルを開かずにシート名をチェック|ExcelマクロVBAサンプル集(3月23日)
数式の参照しているセルを取得する|ExcelマクロVBAサンプル集(3月18日)
CSVの読み込み方法(改の改)|ExcelマクロVBAサンプル集(3月17日)
変数とプロシージャーの命名について|ExcelマクロVBA技術解説(2月12日)

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

1.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
2.ひらがな⇔カタカナの変換|エクセル基本操作
3.RangeとCellsの使い方|ExcelマクロVBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
5.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
6.変数とデータ型(Dim)|ExcelマクロVBA入門
7.セルの参照範囲を可変にする(OFFSET,COUNTA,MATCH)|エクセル関数超技
8.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
9.CSVの読み込み方法|ExcelマクロVBAサンプル集
10.定数と型宣言文字(Const)|ExcelマクロVBA入門



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

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


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




    ↑ PAGE TOP