Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


6736 / 13644 ツリー ←次へ | 前へ→

【43413】SpecialCellsの限界って・・・。 Jaka 06/10/13(金) 15:51 質問[未読]
【43422】Re:SpecialCellsの限界って・・・。 ichinose 06/10/13(金) 22:30 発言[未読]
【43423】Re:SpecialCellsの限界って・・・。 [名前なし] 06/10/13(金) 23:26 発言[未読]
【43425】Re:SpecialCellsの限界って・・・。 Ap 06/10/14(土) 9:14 発言[未読]
【43447】Re:SpecialCellsの限界って・・・。 Jaka 06/10/16(月) 13:58 お礼[未読]

【43413】SpecialCellsの限界って・・・。
質問  Jaka  - 06/10/13(金) 15:51 -

引用なし
パスワード
   下記コード、テストデータ作成コードでデータ作成後、

A列は、2行おきに「1」を計8192個。
B列は、A列と同じパターンでB24573に「1」を増やしたもの。
C列は、A列と同じパターンでB24574に「1」を増やしたもの。
D列は、D1:D24576まで「1」。
E,F列は、2行筒1行おきにD24576辺りまで「1」を書き込んだもの(1行分づらしてありますけど)

手作業で、1列づつ選択して、(マクロでやると終わりそうにないので)
編集 → ジャンプ で、数値、もしくは定数でセルを選択するとC、E、F列は範囲ががでかすぎるとエラーになります。
問題は、B列とC列のエラーの原因が、どこがどうちがうって言うんだ感じでした。
エラーになる基準って何なのでしょうか?
今まで気にも止めてなかったので、でたらめ回答をしていたことになりますけど...。
ますます解らなくなって来ました。

Sub datamake()
Range("A1:F24576").ClearContents
Range("A2:C2").Value = 1
Range("D1:D3").Value = 1
Range("E1:E2").Value = 1
Range("F1,F3").Value = 1

Range("A1:F3").AutoFill Destination:=Range("A1:F24576"), Type:=xlFillDefault
Range("B65536").End(xlUp).Offset(-2).Value = 1
Range("C65536").End(xlUp).Offset(-1).Value = 1
End Sub

【43422】Re:SpecialCellsの限界って・・・。
発言  ichinose  - 06/10/13(金) 22:30 -

引用なし
パスワード
   ▼Jaka さん:
こんばんは。
すごいことに気が付きましたね!!
これ、目安箱ものですね。

Jaka さんの事例を参考に私も少し実験してみました。

>編集 → ジャンプ で、数値、もしくは定数でセルを選択するとC、E、F列は範囲ががでかすぎるとエラーになります。

とありますが、コードだとエラーになりません(Excel2002で確認)。

つまり、

Jaka さんのdatamakeで作成したサンプルデータのC列は
手動操作ではエラーになりますが、

sub test()
  Range("c:c").SpecialCells(xlCellTypeConstants).Select
end sub

というコードでは、エラーにならず、C列全体を選択してしまいますね!!


Jaka さんの事例を試した限りでは、8192という数字がエラーになる境界値に
見えます。

例えば、新規ブックの

標準モジュールに

'======================================================================
Sub 準備()
  Const 限界値 = 8192
  Dim idx As Long
  Cells.ClearContents
  For idx = 1 To 4
    With Range(Cells(1, idx), Cells((idx + 1) * 限界値, idx))
    .Formula = "=if(mod(row()," & idx + 1 & ")=0,"""",1)"
    .Value = .Value
    End With
    Next
End Sub
'========================================
Sub テスト()
  Dim rng As Range
  Set rng = Selection.SpecialCells(xlCellTypeConstants)
  MsgBox rng.Areas.Count
End Sub


「準備」というプロシジャーを実行してみてください。

アクティブシートのA列〜D列にサンプルデータを作成します。

A列〜D列それぞれ単独列を選択した状態
(例えば、A列だけ選択する、B列だけ選択する)で「テスト」を実行してみてください。

「テスト」は、SpecialCellsメソッドを実行後、取得できたセル範囲のArea数を

表示するプロシジャーですが、A列を選択した状態で「テスト」を実行した時は、

「8192」という結果を得ることが出来ますが、

B,C,D列では「1」と表示され正しく作動していません。

A列でも セルA16385に「1」を入力して再度、

A列を選択した状態で「テスト」を実行した時は、

「1」と表示され正しく作動していません。


SpecialCellsメソッドで取得されたセル範囲のArea数が8192を超えると
エラーになるらしいことが想像されます。

ところがB列〜D列も
SpecialCellsメソッドで取得されたセル範囲のArea数は「8192」になるように
作成したサンプルです。

???ですね!!

再度、新規ブックの標準モジュールに

'=====================================================================
Sub 準備2()
  Const 限界値 = 8192
  Dim idx As Long
  Cells.ClearContents
  For idx = 1 To 4
    With Range(Cells(1, idx), Cells((idx + 1) * (限界値 - 1), idx))
    .Formula = "=if(mod(row()," & idx + 1 & ")=0,"""",1)"
    .Value = .Value
    .Cells((idx + 1) * (限界値 - 1) + 1).Value = 1
    End With
    Next
End Sub
'====================================================================
Sub テスト()
  Dim rng As Range
  Set rng = Selection.SpecialCells(xlCellTypeConstants)
  MsgBox rng.Areas.Count
End Sub


として、準備2を実行した後、前者同様に
A列〜D列それぞれ単独列を選択した状態
(例えば、A列だけ選択する、B列だけ選択する)で「テスト」を実行してみてください。

今度は、A列〜D列まで「8192」と表示されるはずです。
(時間はかかりますよ、私の環境でB列で30秒、C列で55秒、D列で72秒)

では、準備 と準備2 ではどこが違うのか??

SpecialCellsメソッドで取得されるセル範囲の
Area数は、準備 と準備2は、共に同じ8192です。

違いはAreas(Areas.Count).countの値です。

つまり、B〜D列で「1」が連続する最後のセル範囲の数が1以上か1の違いです。

準備  

B列  B24574:B24575がB列で最後に「1」が連続するセル範囲でセルの個数 2
C列  C32765:C32767がC列で最後に「1」が連続するセル範囲でセルの個数 3
D列  D40956:D40959がD列で最後に「1」が連続するセル範囲でセルの個数 4
 
いづれもセルの個数が複数あります。


準備2

B列  B24574がB列で最後に「1」が連続するセル範囲でセルの個数 1
C列  C32765がC列で最後に「1」が連続するセル範囲でセルの個数 1
D列  D40956がD列で最後に「1」が連続するセル範囲でセルの個数 1


この違いがArea数が8192でもエラーになるか否かの境界に思えます。

因みにArea数が8191の時は、最後のAreaのセルの個数は複数でも正常に
SpecialCellsメソッドは処理されます。

私は、ExcelだけでDBもどきの処理をするのはせいぜい5000件以下と
決めていますから、過去作成したプログラムがこの現象を危惧することは
ないと思いますが、これはメモメモですね!!

勉強になりました。ありがとうございます。

【43423】Re:SpecialCellsの限界って・・・。
発言  [名前なし]  - 06/10/13(金) 23:26 -

引用なし
パスワード
   Googleで、「SpecialCells 8192」で検索すると、
英語のサイトがやたら出てきます。

【43425】Re:SpecialCellsの限界って・・・。
発言  Ap  - 06/10/14(土) 9:14 -

引用なし
パスワード
   ▼[名前なし] さん:

http://support.microsoft.com/default.aspx?scid=kb;ja;832293

これですね。

【43447】Re:SpecialCellsの限界って・・・。
お礼  Jaka  - 06/10/16(月) 13:58 -

引用なし
パスワード
   ichinoseさん、[名前なし]さん、Apさん、情報ありがとうございました。

▼ichinose さん:
>>編集 → ジャンプ で、数値、もしくは定数でセルを選択するとC、E、F列は範囲ががでかすぎるとエラーになります。
>とありますが、コードだとエラーになりません(Excel2002で確認)。
>コードでは、エラーにならず、C列全体を選択してしまいますね!!
すみません。省いて書いてしまいました。
Win98se & EXL2000SP1 も同じでした。

>ところがB列〜D列も
>SpecialCellsメソッドで取得されたセル範囲のArea数は「8192」になるように
>作成したサンプルです。

>???ですね!!
はい。
私も8192Area数じゃないかと思ったんですが、
私の記載データ条件で最後のセルが固まりなのかどうなのかで変わっているし、
前に住所の都道府県、市区町村分けマクロを作ってみようとして、郵政省の住所データを、都*都とかで市*町*町だっけかでフィルタして、間違えやすい住所を抽出した時に数万件だったかのデータを抽出できたよなぁ?と言うのもあって、1がいに8192エリア数とのいえないんじゃないかとこんがらがりました。
抽出したデータの最初と最後の部分しか比較してないので、確実はないけれど....。
おまけに、どう抽出したのかも覚えてないし(類似住所データを手動で作っていた)、まともな件数も覚えてない状態です。
本日、帰ってから一昨日からのRPGに一心不乱にならなければ、過去に作ったデータを調べてみたいと思います。
MSももっと具体的に説明してくれれば良いのに、手〜抜きやがって.....。
後でichinoseさんのも検証してみたいと思います。

PS
[#8768]でも、SpecialCellsの不具合っていうのかわかったんだけど。(使い方の間違いなんだけど...)
使ってびっくりの結果が返って来ました。
(注)数式がまばらに入っているとしてです。

With ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
  .Value = .Value
End With

6736 / 13644 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free