仮に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ベストアンサー
- 回答日時:
>結合セルに入力があっても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さんへバトンタッチ。。(^^;;;
ではでは。
No.10
- 回答日時:
あ
ども、onlyromさん、#8のcjです。
沢山勉強させて頂いてます。
また迷子になったら助けてください。
> バトンタッチ
って、、、もうネタないし、、、(^^;
と思ったら、幸いにも(?)訂正箇所を発見しました。
#8のネタ
> 1■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
- 回答日時:
お邪魔します
ネタ
1■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)】が全ての基準。
【左上セル】と【列の数】でインデックスが指す意味も変わる。
2■
解りにくくなっている原因のひとつは
.SpebialCells.Areas
が、どんな規則性で振り分けされるか
左→右という単純なものではなく、、、
上→下、行の連続が多い矩形範囲、
左→右、列の連続が多い矩形範囲、
みたいな優先順、ということが言えそうです。
が、調べても"役に立つ機会がない"ようなので、
深く考えないで受け容れることに決めてます。
3■.SpebialCellsの挙動。
.SpebialCellsは必ず、Selection_Changeイベントを伴う
ということから、
単純にExcelのジャンプ機能をバックグラウンドで実行して
その結果で得られるSelectionを返す
のかな?というのが私の推測。
Excelの機能だから、
Intersect(Activesheet.UsedRange, 指定した範囲)
を対象にして実行されます。
=SUM(A:A)のような式でも全行計算する訳ではないですよね。
※やや確度の低い話ですが
□以上3点
ひとつずつ整理しないと混乱しちゃうんじゃないかな。
マージの話はスルーします
(酔)
おそい時間にありがとうございます。
興味深い解説に感謝いたします。
昨夜はちょいと焼酎をいただきまして、ご回答をいただいた時間には爆睡しておりました・・・・。
(///▽///)
No.7
- 回答日時:
恐らくこれが最終回答になるでしょう。
結合セル: B2:B3
空白セル: A2,A3,A5
■質問者提示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
- 回答日時:
最後といいながら、気になるので、、、
結合セル: B2:B3
空白セル: A2、A3、A5
この場合
A2 > B2 > A3 > A5 と進むべきですが、
提示のコードでは
A2 > B2 > A3 > 【B3】 > A5
と、結合セル(B2:B3)に2回飛びませんか?
ということです。
試しにtest6666,7777を実行してみると
D列の結果を見てもそうですが、
Selectされていく様子をみてもそうなるのですが。
質問者の意図していることと当方の思ってることが違う???
夜にでも。後1回ほど登場するやも知れません。。(^^;;;
なるほど・・・・。
セル単位でまわしてるのでこれはやむを得ないのかと思っていましたが、これを回避する手段があるということですね!?
No.5
- 回答日時:
終わりにしようかな、と思ったのですが、
回答2のお礼の、Test6666 を見たので、一言。
Test6666のコードは、条件によっておかしな動作をする場合があることをお気づきでしょうか。
簡単のために、
結合セルを、B2:B3(縦に結合)の1箇所とします。
空白セル: A2,A3,A5、B2:B3(結合セル)
要するに、結合セルと同じ行のセル(A2,A3)も空白の場合ということです。
この条件で、お礼のTest6666を実行してみてください。
どうでしょう?
そういう条件はないんだよ~、という場合は無視願います。
何度もご丁寧なご指導、痛み入ります。
さっそくご教示の例でやってみましたが「おかしな動作」というのが出ていません。(写真添付)
Test6666もTest7777も同じ動きで、同じアドレスを返してくれますが・・・・。
結合セルのアドレスを、個々のセルとして返すことなら、結合セルと同じ行のセル(A2,A3)の空白の如何を問わずそうなります。
はて、なんでしょう?
( ̄~ ̄;)う~ん
No.4
- 回答日時:
しつこく登場、onlyromです。
。。(^^;;;回答1の補足を見てませんでしたのでそれについて。
>ええ、なんとなくはわかるというか、うまく選択されず
>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
'-------------------------------
回答終了。
この回答への補足
なんか、やっていてなんてずいぶん馬鹿な方法に固執したのかなと・・・・。
Σ(~∇~;)
要は、セル範囲がとびとびの場合は、すなおにForEachを使って
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の不思議な動きに気が付いてよかったと思っております。
ありがとうございました。
覚えの悪い生徒のために何度も何度もすみません。
試してみました。
不思議な動きをするんですね。
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
ありがとうございます。
No.2
- 回答日時:
>myRng.Areasですと、とびとびでなく連続したセル範囲は1つの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
- 回答日時:
例えば、B1とA9:A10の3セルをブランクして
次のコードを実行してみると何となく分かると思いますが。
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です。
この回答への補足
> 次のコードを実行してみると何となく分かると思いますが。
ええ、なんとなくはわかるというか、うまく選択されず、myRng外を選択してしまうことはわかります。
どうしてかなあ????
不思議です。
onlyrom先生、いつもありがとうございます。
myRng.Areasですと、とびとびでなく連続したセル範囲は1つの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
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) ExcelVBAで、index、match関数を使用して、指定範囲に出力したい 3 2022/10/18 21:53
- Visual Basic(VBA) ExcelVBAのマクロについて。 9 2022/05/04 14:50
- Excel(エクセル) 日付で矢印マクロ 4 2023/07/25 16:47
- Excel(エクセル) VBA 特定の列に入っているテキストをコピペ 2 2023/06/14 11:24
- Excel(エクセル) B列に文字がはいったらA列に数字が入るマクロードを完成させたい 4 2023/04/21 01:58
- Visual Basic(VBA) 前回ご教授いただいたコードに覚えたてのループ処理で品名りんごAから順に20回for nextでループ 7 2023/01/13 22:01
- Visual Basic(VBA) Sheet2からオートフィルターで売上日を抽出した件数をカウントし、その件数をSheet1のセルB1 2 2023/01/12 12:24
- Excel(エクセル) Excel VBA 空白行があるセル範囲に色を付ける 3 2022/06/13 15:58
- Visual Basic(VBA) 【VBA】写真の貼り付けコードがうまく機能しません。 5 2022/09/01 18:43
- Excel(エクセル) VBA 選択範囲とUnionの使い方について 8 2023/08/17 13:25
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
IF関数で空欄("")の時、Null...
-
エクセルでCSVを編集するとき、...
-
数式による空白を無視して最終...
-
VLOOKUP関数について
-
excel2010 空白セルにのみ貼り...
-
空白セル内の数式を残したまま...
-
「データ要素を線で結ぶ」がチ...
-
エクセルで、「複数のセルの中...
-
《Excel2000》SUMPRODUCT関数で...
-
ピボットテーブルで空白セルの...
-
【Excel】 Ctrl+方向キー で空...
-
Excel > ピボットテーブル「(空...
-
エクセルのグラフで式や文字列...
-
形式貼り付けの「空白を無視す...
-
Excelで、入力文字の後に自動で...
-
数式の結果が空白の時の空白扱い
-
空白セルにハイフンを表示
-
エクセルで入力した文字列を他...
-
Excel:関数が入っているセルに...
-
空白を0とみなす関数
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
IF関数で空欄("")の時、Null...
-
数式による空白を無視して最終...
-
excel2010 空白セルにのみ貼り...
-
エクセルでCSVを編集するとき、...
-
「データ要素を線で結ぶ」がチ...
-
Excelで、入力文字の後に自動で...
-
ピボットテーブルで空白セルの...
-
エクセル 連番が途切れていると...
-
関数TRANSPOSEで空白セルを0に...
-
空白セル内の数式を残したまま...
-
Excel > ピボットテーブル「(空...
-
エクセルで、「複数のセルの中...
-
SUMIFS関数で「計算式による空...
-
空白を0とみなす関数
-
【Excel】 csvの作成時、空白セ...
-
エクセルで上の行の値を自動的...
-
《Excel2000》SUMPRODUCT関数で...
-
一列の中の金額を他のセルに
-
形式貼り付けの「空白を無視す...
-
エクセルで空白文字の前後を入...
おすすめ情報