Excel VBAの範囲の取得について

質問

Excel VBAを使い、ある範囲に含まれるセル番号を取得しようとしています。
まず、ある範囲、の指定方法から悩んでいます。

ある範囲を、A1：C5とした場合、
１．A1:C5に同じ文字列を入力し、それを判別して同じ範囲内だと判断させる。
２．A1:C5の範囲に名前を付ける。
３．A1:C5のセルを結合する。

などの方法を思いつきました。
それ以降の処理はA1:C5の範囲だと、『左上はA1,右下はC5』という風に、範囲を作っている左上と右下のセル番号を参照するようにしたいと思っています。

分からなくなってしまっているのは、他に範囲として扱いたい数個のセルが別にも幾つかあって、それを最初に認識した範囲とは別物だと扱いたいと思っています。

たとえば、先ほどA1:C5という範囲を決めましたが、D2:E4という別の範囲があるとします。

A1から順に調べて行って、C5までが同じ範囲と判断されたら、また次のひと塊の範囲はないか調べ、D2:E4が見つかったら処理をする、という感じなのですが、一度調べた範囲はもう調べないとする方法が思いつきません。
ひとくくりにしたい範囲は、毎度変わりますし、範囲に含まれるセルの数もまちまちです。

上記の、範囲を決める３つの方法それぞれによって、その後の方法も変わってきそうな感じがするのですが、どれが一番適当な方法かも決めかねています。
範囲指定しようとしているセルが一つのみだった場合、結合している範囲かどうかを調べる方法は使えない気もします。

分かりづらい質問で申し訳ありません。
よろしくお願いいたします。

回答No.5

Randomize · ベストアンサー

補足情報として以下の3こについて解決案を書きます
　1.範囲選択の4個目の手段
　2.左上と右下の簡単なアドレスの取得方法
　3.一度調べた範囲の重複を調べる方法

○1.範囲選択の4個目の手段
Excelの内蔵機能を使用してExcelに範囲の判断をしてもらう方法があります。しかし、補足画像に示すような弱点もありますので注意してください。
Excelには空白セルであるか何か入力されているセルかを判断してどこまでのセルの範囲を使用しているかを影で取得しています。(?Sheet1.UsedRange　とイミディエイトウィンドウに書き込んでEnterを押してみてください)　UsedRangeプロパティーは書き込んだ一番右下のセルまでの範囲を返すのですが、これに似たようなプロパティーでCurrentRegionプロパティーというものがあります。
これは、とある基準のセルから上下左右に探していって完全に空白である行と列に当たるまでの範囲を取得するものです。下の図を参照してくださいね。利点としては、その範囲の中に書いてある文字や数式が何でもあってもOKであることです。空白かそうでないかで判断してますしね。逆に空白かそうでないかで判断しているだけですので、間に1セルでも隣接してしまうと、2つの範囲がくっついて認識されてしまいます。補足の図を参考にしてください。しかし、ループなどの下準備なしで範囲を取得できるので弱点を理解して使うと結構便利であると思います。使い方はこうです。
[シート名].Range([アドレス指定]).CurrentRegion
アドレス指定したセルを基準にそのセルを含むかそのセルに隣接する範囲をRangeオブジェクトとして取得するものです。
(例)MsgBox Sheet1.Range("A3").CurrentRegion.Address
くらいで実験で試されてみると分かりやすいと思います。

○2.左上と右下の簡単なアドレスの取得方法
kyboとちょっと回答がかぶるのですが、これも比較的簡単に出すことができます。Cellsプロパティーを使いましょう。
kuboさんは.Cells(行番号,列番号)という呼び出し方をしてますが、Cellsプロパティーはもうちょっと面白い使い方があります。範囲を選択したときにRangeオブジェクトの中にはその範囲の数だけ分の情報が格納されます。そして、1から始まって右へ2・3・4・・・と続き、範囲の右端まで来ると1段下へ移動してその一番左から5・6・7・・・と続くのです。
ですので、左上の場合は.Cells(1)で求められ、右下は.Cells([セル範囲].Count)で求められます。
具体的な例を下に書きますね。(使い方を分かりやすくするためにあえてWithステートメントを使用しません。)
Set objRange = Sheet1.Range("A1").CurrentRegion
'左上の座標を求める
AddrLeftX=objRange.Cells(1).Column
AddrTopY=objRange.Cells(1).Row
'右下の座標を求める
AddrRightX=objRange.Cells(objRange.Count).Column
AddrBottomY=objRange.Cells(objRange.Count).Row
アドレスの文字が欲しい場合は.Addressプロパティーを使ってね
余談：Cellsの括弧の中の数字をセルの数よりも多い数字を指定すると・・・・・・？

○3.一度調べた範囲の重複を調べる方法
これを解決するにはDictionaryオブジェクトを使用するといいです。具体的には検索をしてもらいたいのですが、なにそれ？って場合は「連想配列」で検索してみてください。
あくまで一例なのですが、範囲を取得したときのアドレスをキーワードにしてリストへ保存していきます。次の範囲が見つかったら、リストを見てもらってこの範囲ってリストにあった？と確認して重複をチェックしようとするのが狙いです。
Dictionaryオブジェクトの作り方などはほかのサイトを参照してください。作った後、objDicAddrListと仮に名前をつけると
アドレスをリストへ追加するときは
objDicAddrList.Add [アドレス文字],[値]
という感じに使ってください。アドレス文字はobjRange.Addressで取得できると思います。値は今回の目的では使用しませんので好きな値を入れるなりして有効に使ってあげてください。
そして、調べようとしているアドレスがリストに存在するかを調べるのが
objDicAddrList.Exists([調べたいアドレス文字])
です。リストに存在する場合はこれがTrueに、ない場合はFalseが返ってきます。これをIfで判断すると簡単に重複のチェックができます。

以上が説明になります。質問の内容がはっきりしないため、私なりでどういうことがしたくてその時に問題になりそうな部分のみを補足で説明しました。弱点を克服する範囲を求める方法になると、塗りつぶしアルゴリズムなどの使用も必要になってくるでしょう。ですが、塗りつぶしアルゴリズムは非常に説明が長いのでどうしても必要になった時に調べてみてください。では健闘を祈ります。
添付画像は完成まで少々お待ちくださいね。今からささっと作りますゆえ・・・

お礼

ご回答ありがとうございました。
また、質問の内容が分かりづらく申し訳ありませんでした。
CADとの連携を考えていたため、具体的にすることにより、回答が得られにくいのではと考えてしまいました。すみません。
ぜひ、参考にさせていただこうと思います。

回答No.4

kybo

結局したいのは

>それ以降の処理はA1:C5の範囲だと、『左上はA1,右下はC5』という風に、範囲を作っている左上と右下のセル番号を参照するようにしたいと思っています。

この部分のみなのでしょうか？

Sub macro1()
With Range("A1:C5")
MsgBox "範囲の左上は：" & .Cells(1, 1).Address
MsgBox "範囲の右下は：" & .Cells(.Rows.Count, .Columns.Count).Address
End With
End Sub

のようにします。

範囲が複数あった場合、以下のようにします。

Sub macro2()
Dim C As Range
’複数範囲を選択したと仮定
Range("A1:D10,G5:I15").Select
For Each C In Selection.Areas
MsgBox "範囲の左上は：" & C.Cells(1, 1).Address
　’黄色で色を付ける
C.Cells(1, 1).Interior.ColorIndex = 6
MsgBox "範囲の右下は：" & C.Cells(C.Rows.Count, C.Columns.Count).Address
　’黄色で色を付ける
C.Cells(C.Rows.Count, C.Columns.Count).Interior.ColorIndex = 6
Next C
End Sub

お礼

ご回答ありがとうございました。
また、質問の内容が分かりづらく申し訳ありませんでした。
CADとの連携を考えていたため、具体的にすることにより、回答が得られにくいのではと考えてしまいました。すみません。
ぜひ、参考にさせていただこうと思います。

回答No.3

mitarashi

なさりたい事をストレートに書いていただいた方が、なけなしの想像力を酷使しないで済むので助かりますが、
Sub test()
Dim myRange(2) As Range
Dim i As Long

Set myRange(1) = Range("A1:C5")
Set myRange(2) = Range("D2:E4")
For i = 1 To 2
Debug.Print myRange(i).Address
Next i
End Sub

Sub test2()
Dim myRange As Range, myArea As Range

Set myRange = Range("A1:C5")
Set myRange = Union(myRange, Range("D2:E4"))
For Each myArea In myRange.Areas
Debug.Print myArea.Address
Next myArea
End Sub
といった事でしょうか。

お礼

ご回答ありがとうございました。
また、質問の内容が分かりづらく申し訳ありませんでした。
CADとの連携を考えていたため、具体的にすることにより、回答が得られにくいのではと考えてしまいました。すみません。
ぜひ、参考にさせていただこうと思います。

回答No.2

hana-hana3

何がしたいのか良く解りませんが・・・。
選択範囲（複数）のセル範囲アドレスを知りたいと言うことでしょうか？

セル選択範囲の取得
http://www.asahi-net.or.jp/~ef2o-inue/vba_k/sub04_040_07.html

お礼

ご回答ありがとうございました。
また、質問の内容が分かりづらく申し訳ありませんでした。
ぜひ、参考にさせていただこうと思います。

回答No.1

camputer

こんにちは．
＞ある範囲、の指定方法
でしたら，Rangeオブジェクトで作れます．

Sub For_WANKO_P()
　Dim 範囲 As range
　Dim セル As range
　Set 範囲 = Worksheets("sheet1").range("A1:C5")
　For Each セル In 範囲
　　MsgBox (セル.Column & "-" & セル.Row)
　　　　　'「範囲」内にあるすべてのセルの行番号と列番号表示
　Next
End Sub

＞ある範囲に含まれるセル番号を取得
については，↑のFor～Nextの要領でいかがでしょう．
セルの値自体を取りたい際は，ColumnをValueに変えるとできます．
これでやりたいことが出来ればいいのですが．．

お礼

ご回答ありがとうございました。
また、質問の内容が分かりづらく申し訳ありませんでした。
ぜひ、参考にさせていただきます。