VBAの二次元配列？

今VBAの勉強中です。
VBAの高速化で二次元配列？というものがあるということで勉強しようと思っていますが、
良く理解出来ていません。
例えば、
下記はSheet1のB列の値が0以外であれば、その行をSheet2に順次コピーしていくというもので
自分なりに作成してみました。
これを二次元配列？で一気に処理する場合、どのようにすれば良いかご教授頂けないでしょうか。

p = 2
For i = 1 To 100000
If .Cells(i, "B").Value <> 0 Then
Sheets("Sheet1").Rows(i).Copy Sheets("Sheet2").Rows(p)
p = p + 1
End If
Next i

No.4 ベストアンサー 回答者： tom04 回答日時： 2020/04/09 18:54

No.2です、

お礼欄のコードを拝見すると、B列だけの操作でよいのですね。
ただ、今回の質問に関しては配列で処理するのは不向きのような気がします。

どうしても！というコトであれば・・・
前回のコードのmyRはループを高速にするために元データを単に配列にしただけなので
該当データを格納する箱（入れ物）を用意する必要があります。
（直接セルに表示する場合は該当データを格納する必要がないので、不要です）

最初に記載したように無理やりになりますが、やってみました。
B列の「0」以外のデータをC列に表示するとします。

Sub Sample2()
　Dim i As Long
　Dim lastRow As Long
　Dim cnt As Long
　Dim myR1, myR2

　　lastRow = Cells(Rows.Count, "A").End(xlUp).Row
　　　myR1 = Range(Cells(1, "B"), Cells(lastRow, "B"))
　　　myR2 = Range(Cells(1, "C"), Cells(lastRow, "C")) '//←表示先の範囲をとりあえず決めておく（多めでも大丈夫）★//
　　　　For i = 1 To UBound(myR1, 1)
　　　　　If myR1(i, 1) <> 0 Then
　　　　　　cnt = cnt + 1
　　　　　　　myR2(cnt, 1) = myR1(i, 1)
　　　　　End If
　　　　Next i
　　　Range(Cells(1, "C"), Cells(cnt, "C")) = myR2 '//←C列に一気に吐き出す//
End Sub

※　通常は上記のようなことはあまりやりませんが、無理やりやってみました。

まずはこの程度で・・・m(_ _)m

この回答へのお礼

ありがとうございます。
配列の事少しずつ分かってきました。
変な例を挙げてしまい申し訳ございません。
ご教授頂いた内容を基に勉強していきます。

お礼日時：2020/04/10 11:35

No.3 回答者： Qchan1962 回答日時： 2020/04/09 01:21

ご質問を拝見する限り、めぐみん_さんの方法が望ましく配列処理に適していない内容と思います。

>これを二次元配列？で一気に処理する場合、どのようにすれば良いかご教授頂けないでしょうか。
一気に処理する場合、不具合なく処理する事は出来ないと思います。（空白でない列が千行、B列の０が千程度ある想定）

私も詳しい訳でないので、教えられるレベルではありませんが、配列処理に向かない（するべきでない）処理もあると思います。
向いている機能を使うのもExcelVBAの使い方かとこの場合、、めぐみん_さんの代案処理

向かない理由としては、
～.Rows(1),～.Rows(lastRow) の様に
範囲として一度に配列に代入するのは問題があります。
Set でRangeとして代入でも、、、
また、
If .Cells(i, "B").Value <> 0 Then
Sheets("Sheet1").Rows(i).Copy Sheets("Sheet2").Rows(p)
にもあります。
行を忘れて、セルの範囲としても
条件.Cells(i, "B").Value <> 0 によってSheets("Sheet2")にSheets("Sheet1")の行がコピペされますが
0の時は、コピーされません。
値だけで良いなら、（xlPasteValues）配列で良いですが、.Rows(i).Copy Sheets("Sheet2").Rows(p)なので書式が含まれています。
従って、違う書式がある場合を想定して、その度に書式を設定またはコピペしなくてはなりません。
（すべて同じ書式なら最後に書式コピペでも良いですが）

その度にコピペするなら、配列で一度に書き出す最大のメリットをあまり生かせません。
条件設定が無いのであれば、範囲のコピペなのでそもそも配列もループもいりませんしね。

この回答へのお礼

ご回答ありがとうございます。
配列処理にも向き不向きがあることですね。
詳細なご説明ありがとうございました。

お礼日時：2020/04/09 18:28

No.2 回答者： tom04 回答日時： 2020/04/08 21:58

こんばんは！

簡単に解決したい場合はNo.1さんがおっしゃっているような方法が手っ取り早いと思いますが、
今回は「配列」に関しての質問が主だと思いますので・・・

お示しのコードはコピー＆ペーストとなっていますが、
配列は高速で処理する場合によく使用しますが、あくまで「値」のみの操作になってしまいます。
すなわちコピー＆ペーストではありません。

A1～A列最終行（仮に10万行目まで）のデータがあり、B1セル以降に1から連番を入れるとします。
単純にFor～Nextで1～100000までループさせると相当の時間を要してしまいます。

それを配列を使ってやると↓のようなコードで一瞬で可能です。

Sub Sample1()
　Dim i As Long
　Dim lastRow As Long
　Dim myR '//←配列はValiant型で宣言（何も宣言していないので、Valiant型になる）//

　　lastRow = Cells(Rows.Count, "A").End(xlUp).Row
　　　myR = Range(Cells(1, "B"), Cells(lastRow, "B")) '//B1～最終行までを配列に格納（「Set」は付けない！）★①//
　　　　For i = 1 To UBound(myR, 1)
　　　　　myR(i, 1) = i
　　　　Next i

　　　'//▼セル上に一気に吐き出す//
　　　Range(Cells(1, "B"), Cells(lastRow, "B")) = myR '//★②//
End Sub

上記コードの「★①」でをB1～B100000の範囲を配列に格納できます。
※　本来はセル範囲ではなく、行数（列数）で配列とします。

配列内でループさせる方がセル範囲を「Set」し、セルを順にループさせるより格段に速く処理できます。

ただ、「★②」の処理をしないと単に配列にデータがあるだけで、
Sheet上には何も表示されないので、一気にSheetに吐き出します。

上記説明は1列だけの説明でしたが、複数列も配列にするコトが可能です。（二次配列）
というわけでセルのある範囲を行数・列数を指定することでその範囲内を高速にループさせることが可能だというコトになります。

※　注意点として、極端に広い範囲（行数・列数）を配列にして、その中でループさせてしまうと
「メモリが不足しています」となりますので、ほどほどの行・列数を配列とした方がよいと思います。m(_ _)m

この回答へのお礼

ご回答ありがとうございます。
ご丁寧なご説明により少しづつ分かってきました。また、値のみの操作という事
理解出来ました。

おそらくmyR(i, 1) = iの部分でmyRに値(i)を格納していき、それを最終行で一気に
吐き出すという事だと考えて、B列で0以外のみを格納して一気に吐き出すことを
試しにやってみたいと思い、下記のようにしてみましたが、
B列全ての値が吐き出されてしまいました。
私の思慮不足部分があると思いますが、根本間違っているでしょうか？

Sub Sample1()
Dim i As Long
Dim lastRow As Long
Dim myR
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
myR = Range(Cells(1, "B"), Cells(lastRow, "B"))
For i = 1 To UBound(myR, 1)
If Cells(i, "B").Value <> 0 Then
myR(i, 1) = Cells(i, "B").Value
End If
Next i
Sheets("Sheet2").Select
Range(Cells(1, "B"), Cells(lastRow, "B")) = myR
End Sub

お礼日時：2020/04/09 18:26

No.1 回答者： めぐみん_ 回答日時： 2020/04/08 19:53

二次元配列に纏めて入れたとしても、０か否かは結局１個１個調べるのは同じ。

（多少は早いかもだけど更に別の配列を用意しなきゃだし）
この場合ならフィルタをかけて 0以外の値を表示させておいて『可視セル（見えているセル）』をコピペするのが速そうに思うけど。

この回答へのお礼

ご回答ありがとうございます。
今回挙げた例でいうと確かにフィルタで対応する方が良さそうです。
すみません、例が悪かったです。

お礼日時：2020/04/09 18:20