エクセルVBAで、とびとびのセルの順次選択方法？！

質問

仮にA1：B10という範囲内で、空白のセルだけを一つずつ順番に選択しようと思い、以下のコードを書いてみました。
実行してみると、範囲内がすべて空白の場合にはA1→B1→A2→B2→・・・と、選択してくれます。
しかし、空白と空白以外のセルが混在していると、最初の空白セルから下に、範囲内の空白セル数分だけ、空白であると否とを問わず選択してしまいます。
（￣□￣；）！！
myRngには空白セルだけを指定され、myRng.Cells.Countでもちゃんと空白セル数がカウントされます。
でも、myRng.Cells(i).Select　では正しく選択されないのはなぜでしょうか？
Cells(i)を使用せず、ループを
For Each c In myRng
c.Select
Application.Wait Now + TimeValue("0:00:01")
Next c
で回せば選択できるのに・・・・・。

Sub test01()
Dim x As Long, i As Long, myRng As Range
With ActiveSheet
Set myRng = .Range("A1:B10").SpecialCells(xlCellTypeBlanks)
x = myRng.Cells.Count
For i = 1 To x
myRng.Cells(i).Select
Application.Wait Now + TimeValue("0:00:01")
Next i
End With
Set myRng = Nothing
End Sub

ご教示くださいませ。　(o。_。)oペコッ.

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.9

onlyrom · ベストアンサー

＞結合セルに入力があってもSpecialCells(xlCellTypeBlanks)でひっかかる

いやー、値はある結合セル、までは考えていませなんだ。
まいった、まいった、というところでしょうか。(^^;;;

＞If c.Address = c.MergeArea(1).Address And c.MergeArea(1) = "" Then　と簡略化してもおなじですよね？？

値なのでちゃんとValueプロパティを使ってちょと短くすると

If c.Address = c.MergeArea.Cells(1).Address And c.Value = "" Then

それから、かねがね当方もそのスキルの高さに敬服しているcj_moverさんの回答にもありますように
SpecialCells(xlCellTypeBlanks)が返す範囲は場合によっては
おい、おい、というようなものになることがありますので、
使うときは、先ず、Addressを表示確認した後に使った方がいいかも知れませんね。

何れにしろ、今回は当方にとっては再確認の旅になりました。
どこまでいってもエラーの出ないmyRng.Cells(i)の振る舞い、など。
とこまでいってもはちょと大袈裟？？？？

ほんとに、ほんとに、ほんとに、これで最後。。。
続きは、cj_moverさんへバトンタッチ。。(^^;;;
ではでは。

お礼

ほんとうにお世話様でした。
これにこりず、またよろしくご指導たまわりますようお願い申し上げます。
　　(o。_。)oペコッ.

回答No.10

cj_mover

あ
ども、onlyromさん、＃８のcjです。
沢山勉強させて頂いてます。
また迷子になったら助けてください。

> バトンタッチ
って、、、もうネタないし、、、(^^;

と思ったら、幸いにも（？）訂正箇所を発見しました。

＃８のネタ
> １■rng(n)
の説明ですが、「nが自然数の場合」、という条件が抜けてました。
ついでなので、(n < 1)の動作を説明代わりのコードにしてアップします。

' //////
Sub MMM(): Dim i%
With Application
　Cells(1).ID = .DisplayCommentIndicator
　.DisplayCommentIndicator = -1&
　With Range("G3:J3")
　　.Select
　　For i = -10 To 12
　　　.Item(i).NoteText StrConv(i _
　　　& vbLf & .Item(i).Address(False, False), vbWide)
　　Next i
Stop ' シート上のコメントの内容を確認後、再実行
　End With
　.DisplayCommentIndicator = CLng(Cells(1).ID)
End With
Range("D1:G3,G3:J5").ClearComments
End Sub
' //////

> ...最後、< なんて云わずに(^^)、さあさあ、、、
（グビグビ）

お礼

cj_mover先生、再度のご登場ありがとうございます。
両大先生の掛け合い、楽しく読ませていただきました。

なんと0や負数でのインデックス指定ができるのですね！おどろきました。
また、その動きが対照的なかたちになってさらにビックリ！
両先生のご指導で今回も大変勉強になりました。
ありがとうございました。

回答No.8

cj_mover

お邪魔します

ネタ

１■rng(n)
書式：セル範囲(インデックス)
で参照する単セルについて
　　【.Areas(1)】の【左上セル】
　　と
　　【.Areas(1)】の【列の数】
このふたつで矛盾なく説明出来るようです。
【.Areas(1)】の【左上セル】
から右へ数えて
【.Areas(1)】の【列の数】で規定される右端まできたら
次の行、ってな具合。
　(Dim rng As Range)
　WCL = rng.Areas(1).Columns.Count
　r = 1 + (n - 1) \ WCL
　c = 1 + (n - 1) Mod WCL
　rng(n) [is] rng.Areas(1).Cells(1, 1).Cells(r, c)

例
　Cells(257) is B1。（Excel2007より前のver.の場合）
　Range("C3:E5")(10) is C6。
　Range("C3:E5,any,...")(10) is C6。（どれだけAreasが増えても同じ）

.SpecialCellsで取得したセル範囲の場合も
【.Areas(1)】が全ての基準。
【左上セル】と【列の数】でインデックスが指す意味も変わる。

２■
解りにくくなっている原因のひとつは
.SpebialCells.Areas
が、どんな規則性で振り分けされるか
左→右という単純なものではなく、、、
上→下、行の連続が多い矩形範囲、
左→右、列の連続が多い矩形範囲、
みたいな優先順、ということが言えそうです。
が、調べても"役に立つ機会がない"ようなので、
深く考えないで受け容れることに決めてます。

３■.SpebialCellsの挙動。
　.SpebialCellsは必ず、Selection_Changeイベントを伴う
ということから、
単純にExcelのジャンプ機能をバックグラウンドで実行して
その結果で得られるSelectionを返す
のかな？というのが私の推測。
Excelの機能だから、
Intersect(Activesheet.UsedRange, 指定した範囲)
を対象にして実行されます。
=SUM(A:A)のような式でも全行計算する訳ではないですよね。

※やや確度の低い話ですが
□以上３点
ひとつずつ整理しないと混乱しちゃうんじゃないかな。

マージの話はスルーします
（酔）

お礼

おそい時間にありがとうございます。
興味深い解説に感謝いたします。
昨夜はちょいと焼酎をいただきまして、ご回答をいただいた時間には爆睡しておりました・・・・。
(///▽///)

回答No.7

onlyrom

恐らくこれが最終回答になるでしょう。

結合セル：　Ｂ２：Ｂ３
空白セル：　Ａ２，Ａ３，Ａ５

■質問者提示Test7777（FoR Each）のコードを一行入れ替え

>If Not c.MergeCells Or (c.MergeCells And c.MergeArea(1) = "") Then

これを

If c.Address = c.MergeArea.Cells(1).Address Then

と入れ替えてみたらどうでしょう。

■Test6666（For Eachでない方法）でもできますが、
For文をネストしないといけないのであまりスマートではないかも。

ではでは。

お礼

さすがonlyrom大先生ですね！
If c.Address = c.MergeArea.Cells(1).Address Then　で、結合してないセルと、結合セルの最初のセルだけという指定ができちゃいますね、すばらしい！
ただ、これをこのまま実行すると、結合セルに入力があってもSpecialCells(xlCellTypeBlanks)でひっかかっているのでまずいです。

If c.Address = c.MergeArea.Cells(1).Address And c.MergeArea.Cells(1) = "" Then　で入力がある結合セルを排除できました。
これを
If c.Address = c.MergeArea(1).Address And c.MergeArea(1) = "" Then　と簡略化してもおなじですよね？？

セル選択のたび、1秒もまってられないのでSleepを使い

Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub Test8888()
Dim c As Range, myRng As Range, myD As Range
With ActiveSheet
Set myRng = .Range("A1:B10").SpecialCells(xlCellTypeBlanks)
Set myD = Range("D1")
myD.Value = myRng.Address
For Each c In myRng
' If c.Address = c.MergeArea.Cells(1).Address And c.MergeArea.Cells(1) = "" Then
If c.Address = c.MergeArea(1).Address And c.MergeArea(1) = "" Then
Set myD = myD.Offset(1)
myD.Value = c.MergeArea.Address
c.Select
Sleep 100
End If
Next c
End With
Set myRng = Nothing
Set myD = Nothing
End Sub

ありがとうございました。

回答No.6

onlyrom

最後といいながら、気になるので、、、

結合セル：　Ｂ２：Ｂ３
空白セル：　Ａ２、Ａ３、Ａ５

この場合

Ａ２　＞　Ｂ２　＞　Ａ３　＞　Ａ５　と進むべきですが、

提示のコードでは

Ａ２　＞　Ｂ２　＞　Ａ３　＞　【Ｂ３】　＞　Ａ５

と、結合セル（Ｂ２：Ｂ３）に２回飛びませんか？
ということです。

試しにtest6666,7777を実行してみると
Ｄ列の結果を見てもそうですが、
Selectされていく様子をみてもそうなるのですが。

質問者の意図していることと当方の思ってることが違う？？？

夜にでも。後１回ほど登場するやも知れません。。(^^;;;

お礼

なるほど・・・・。
セル単位でまわしてるのでこれはやむを得ないのかと思っていましたが、これを回避する手段があるということですね！？

回答No.5

onlyrom

終わりにしようかな、と思ったのですが、
回答２のお礼の、Test6666　を見たので、一言。

Test6666のコードは、条件によっておかしな動作をする場合があることをお気づきでしょうか。

簡単のために、
結合セルを、Ｂ２：Ｂ３（縦に結合）の１箇所とします。

空白セル：　Ａ２，Ａ３，Ａ５、Ｂ２：Ｂ３（結合セル）

要するに、結合セルと同じ行のセル（Ａ２，Ａ３）も空白の場合ということです。

この条件で、お礼のTest6666を実行してみてください。
どうでしょう？

そういう条件はないんだよ～、という場合は無視願います。

お礼

何度もご丁寧なご指導、痛み入ります。
さっそくご教示の例でやってみましたが「おかしな動作」というのが出ていません。（写真添付）
Test6666もTest7777も同じ動きで、同じアドレスを返してくれますが・・・・。
結合セルのアドレスを、個々のセルとして返すことなら、結合セルと同じ行のセル（A2，A3）の空白の如何を問わずそうなります。
はて、なんでしょう？
(￣～￣;)う～ん

回答No.4

onlyrom

しつこく登場、onlyromです。。。(^^;;;

回答１の補足を見てませんでしたのでそれについて。

>ええ、なんとなくはわかるというか、うまく選択されず
＞myRng外を選択してしまうことはわかります。
>不思議です。

当方も以前からそのOffsetもどきの動きが気になってはおりました。
特に範囲を外れてもエラーがでないところなど。

その動きがCellsプロパティの仕様なのか、それともバグなのか
Excelスタッフーーに一度訊ねてみたいものです。

'-------------------------------
For i = 1 To 10
Cells(i, "E").Value = Range("B2").Cells(i).Address(0, 0)
Next i

For i = 1 To 10
Cells(i, "F").Value = Range("B2:D2").Cells(i).Address(0, 0)
Next i
'-------------------------------

回答終了。

お礼

覚えの悪い生徒のために何度も何度もすみません。
試してみました。
不思議な動きをするんですね。

Sub TEST00()
With ActiveSheet
For i = 1 To 10
.UsedRange.Cells(.UsedRange.Count + i).Select
Application.Wait Now + TimeValue("0:00:01")
Next i
End With
End Sub

ありがとうございます。

補足

なんか、やっていてなんてずいぶん馬鹿な方法に固執したのかなと・・・・。
Σ(~∇~；)

要は、セル範囲がとびとびの場合は、すなおにForEacｈを使って

Sub Test7777()
Dim c As Range, myRng As Range, myD As Range
With ActiveSheet
Set myRng = .Range("A1:B10").SpecialCells(xlCellTypeBlanks)
Set myD = Range("D1")
myD.Value = myRng.Address
For Each c In myRng
If Not c.MergeCells Or (c.MergeCells And c.MergeArea(1) = "") Then
Set myD = myD.Offset(1)
myD.Value = c.Address
c.Select
Application.Wait Now + TimeValue("0:00:01")
End If
Next c
End With
Set myRng = Nothing
Set myD = Nothing
End Sub

がいちばん簡単ということですよね。
これからはそうします。
でもCellsの不思議な動きに気が付いてよかったと思っております。

ありがとうございました。

回答No.3

onlyrom

回答２、結果には関係ないですし、
お分かりとは思いますが、削除し忘れが一行あり。

x = myRng.Cells.Count

回答No.2

onlyrom

>myRng.Areasですと、とびとびでなく連続したセル範囲は１つのAreaとなってしまい、セル単位の操作には使えないので以下のようにしてみました

上記のこと、ちょと意味が分からないのですが。。。(^^;;;

ま、あまり難しく考えずに、あっさりと。。。

'----------------------------------------------
Sub Test5555()
Dim x As Long, i As Long, myRng As Range
With ActiveSheet
Set myRng = .Range("A1:B10").SpecialCells(xlCellTypeBlanks)

　Dim j
　x = myRng.Cells.Count
　For i = 1 To myRng.Areas.Count
　　　For j = 1 To myRng.Areas(i).Cells.Count
　　　　　myRng.Areas(i).Cells(j).Select
　　　　　Application.Wait Now + TimeValue("0:00:01")
　　　Next j
　Next i

End With
Set myRng = Nothing
End Sub
'--------------------------------------

こんなふうでも出来そうですが。
勘違いしてましたらご容赦願います。

お礼

onlyrom先生、何度もありがとうございます。
Area内のセル数で分岐させなくともいいわけですね！
ありがとうございます。とても勉強になります。

ただ、いろいろ試すうちに、結合セルがあって、その結合セル内に入力があっても空白セルとされてしまうようです。
そのため、Area内における個々のセルが結合セルかどうかを判定し、さらに結合の場合はそこが空白か否か判定することになり苦心のあげく以下のように分岐せざるを得ませんでした。

Sub Test6666()
Dim i As Long, j As Long, myRng As Range, myD As Range
With ActiveSheet
Set myRng = .Range("A1:B10").SpecialCells(xlCellTypeBlanks)
Set myD = Range("D1")
myD.Value = myRng.Address
For i = 1 To myRng.Areas.Count
For j = 1 To myRng.Areas(i).Cells.Count
If myRng.Areas(i).Cells(j).MergeCells Then
If Application.CountA(myRng.Areas(i).Cells(j).MergeArea) = 0 Then
Set myD = myD.Offset(1)
myD.Value = myRng.Areas(i).Cells(j).Address
myRng.Areas(i).Cells(j).Select
Application.Wait Now + TimeValue("0:00:01")
End If
Else
Set myD = myD.Offset(1)
myD.Value = myRng.Areas(i).Cells(j).Address
myRng.Areas(i).Cells(j).Select
Application.Wait Now + TimeValue("0:00:01")
End If
Next j
Next i
End With
Set myRng = Nothing
Set myD = Nothing
End Sub

ありがとうございました。

回答No.1

onlyrom

例えば、Ｂ１とＡ９：Ａ１０の３セルをブランクして
次のコードを実行してみると何となく分かると思いますが。

Sub Test555()
Dim x As Long, i As Long, myRng As Range
Set myRng = Range("A1:B10").SpecialCells(xlCellTypeBlanks)
x = myRng.Cells.Count
Range("D1").Value = myRng.Address
For i = 1 To x
Cells(i + 1, "D").Value = myRng.Cells(i).Address
Next i
End Sub

例のように、ブランクの範囲が飛び飛びの場合は
For Each Nextでは上手く個々のセルを取得できるのですが、
質問のような単純なFor Nextで回す場合はちょと注意がいるということです。

そうです、ご存知、myRng.Areasです。

お礼

onlyrom先生、いつもありがとうございます。
myRng.Areasですと、とびとびでなく連続したセル範囲は１つのAreaとなってしまい、セル単位の操作には使えないので以下のようにしてみました。
何か間抜けな落とし穴はないですよね？

Sub test03()
Dim x As Long, i As Long, myRng As Range
With ActiveSheet
Set myRng = .Range("A1:B10").SpecialCells(xlCellTypeBlanks)
x = myRng.Areas.Count
For i = 1 To x
If myRng.Areas(i).Cells.Count > 1 Then
For n = 1 To myRng.Areas(i).Cells.Count
myRng.Areas(i).Cells(n).Select
Application.Wait Now + TimeValue("0:00:01")
Next n
Else
myRng.Areas(i).Select
Application.Wait Now + TimeValue("0:00:01")
End If
Next i
End With
Set myRng = Nothing
End Sub

補足

> 次のコードを実行してみると何となく分かると思いますが。

ええ、なんとなくはわかるというか、うまく選択されず、myRng外を選択してしまうことはわかります。
どうしてかなあ？？？？

不思議です。