空白への0入力 二次元配列(VBA)

B列の1行目から232970行目を、空白セルであれば0を入力するという構文を
作成してみました。（配列について勉強中）
これをB列だけでなく、FQ列まで同じように処理を実行したいと考えています。

Dim i As Long
Dim MyArray As Variant
MyArray = Range(Cells(1, "B"), Cells(232970, "B"))
For i = 1 To 232970
If MyArray(i, 1) = "" Then
MyArray(i, 1) = "0"
End If
Next i
Range(Cells(1, "B"), Cells(232970, "B")) = MyArray
End Sub

↓

FQ列まで同じように繰り返しということで、下記のようにしてみました。
これでも動くのですが、二次元配列で行だけでなく列まで配列出来たらもっと
スマート＆速度アップになるでしょうか？
二次元配列についてあまりよく理解できていませんのでその点ご教授頂ければと考えています。

Dim i, s As Long
Dim MyArray As Variant
For s = 2 To 173
MyArray = Range(Cells(1, s), Cells(23297, s))
For i = 1 To 23297
If MyArray(i, 1) = "" Then
MyArray(i, 1) = "0"
End If
Next i
Range(Cells(1, s), Cells(23297, s)) = MyArray
Next s

No.1 ベストアンサー 回答者： Qchan1962 回答日時： 2020/04/17 17:15

>MyArray = Range(Cells(1, "B"), Cells(232970, "B"))

範囲を纏めて入れられるので FQ列までまとめて入れる事も可能です。
セル範囲を配列に入れるので、この場合、配列内は1から始まります　 1 To 172
MyArray(1 to 23297,1 to 172)

　 MyArray = Range(Cells(1, 2), Cells(23297, 173))
　 For i = 1 To 23297
　　For s = 1 To 172
　　　 If MyArray(i, s) = "" Then
　　　　 MyArray(i, s) = "0"
　　　End If
　　Next s
　Next i
　 Range(Cells(1, 2), Cells(23297, 173)) = MyArray

一度に出力するので、書き出し部分の回数分くらい早いかと思います。

この回答へのお礼

早速のご回答誠にありがとうございます。
行だけでなく、列もまとめて配列に入れる事が出来るのですね。
ご教示頂いた内容を試した結果、問題無く実行できました。

お礼日時：2020/04/17 19:13

No.3 回答者： tom04 回答日時： 2020/04/17 18:22

こんにちは！

空白セルには数式は入っていないのですかね？
そうであればNo.2さんがおっしゃっている方法が一番簡単で速いと思います。

質問文では232970行までとなっていますが、コードは23297行までですね。
とりあえず23297行目までとしています。

配列を使わず一気にやる場合は

Sub Sample1()
　On Error Resume Next '//←念のため//
　Range(Cells(1, "B"), Cells(23297, "FQ")).SpecialCells(xlCellTypeBlanks).Value = 0
End Sub

こんな感じでいけると思います。

どうしても二次元配列で処理したいのであれば・・・

Sub Sample2()
　Dim i As Long, j As Long
　Dim myR
　　myR = Range(Cells(1, "B"), Cells(23297, "FQ"))
　　　For i = 1 To 23297
　　　　For j = 1 To Range("FQ1").Column - 1
　　　　　If myR(i, j) = "" Then
　　　　　　myR(i, j) = 0
　　　　　End If
　　　　Next j
　　　Next i
　　Range(Cells(1, "B"), Cells(23297, "FQ")) = myR
End Sub

こんな感じでしょうかね。

※　余談ですが、
個人的に列番号を取得する場合、A列・B列などであればすぐに1や2のように取得できますが
列数が増えた場合は指折り数えたり、今回のように「FQ」列などとてつもない数になりますので、
＞Range("FQ1").Column
のようにすればFQ列の列番号が簡単に取得できます。
（ちなみに「-1」としているのはB列～FQ列が配列の列数になりますので、A列分の「1」をマイナスする必要があります）

なので列数が少なくても、仮にA1セルだとすると自分では
＞Cells(1, 1)
とせずに
＞Cells(1, "A")
のような感じでやっています。
もちろん慣れている方で構わないと思いますが・・・m(_ _)m

この回答へのお礼

早速のご回答誠にありがとうございます。
また、列取得の件もありがとうございます。
頑張って勉強致します。

お礼日時：2020/04/17 19:19

No.2 回答者： 銀鱗 回答日時： 2020/04/17 17:18

それでも良いと思いますが、自分なら別の手順になる。

以下は「マクロの記録」で操作をそのままマクロにしたものです。
　A1セルには「0」が入力されていて、
　それをコピー。
　B1セルからFQ232970セルまでの範囲を選択。
　”ジャンプ機能” で空白のセルを選択。
　そこへ「0」を貼り付け。
　最後にA1セルを選択して終わる。
こんな感じ。

Sub Macro1()
'
' Macro1 Macro
'

'
　Range("A1").Select
　Selection.Copy
　Range("B1:FQ232970").Select
　Selection.SpecialCells(xlCellTypeBlanks).Select
　ActiveSheet.Paste
　Range("A1").Select
End Sub

このほうがスマート。
（でも、これ、マクロにする意味無いんだよなあ…手動でやっても一瞬で終わるし、繰り返し行うこともない）

配列を使っても良いけど、
システムに搭載されているメモリに依存するかもしれないので、
質問にあるような広範囲に対して自分はあまり使わない。

・・・

For ～ Next
の使い方を覚えるなら、質問文にあるやり方で十分です。

Excelに処理速度を求めるなら、Excelが持っている ”標準の機能” を使うと良いでしょう。
記述も簡単になります。

この回答へのお礼

早速のご回答誠にありがとうございます。
確かに今回の処理であればご教示頂いた方法で十分でした。
頑張ります。

お礼日時：2020/04/17 19:14