エクセルVBAで配列内に空白データを入れる場合

質問

エクセル2000です。
ある大きな表のうち、0値を非表示ではなく完全に削除するために以下のようなマクロを書いてみました。
一旦配列に取り込んでいるのは高速化のためです。
これで見た目には目的を達しているのですが、実際には0値が長さ０の文字列に変わっただけで完全な空白にはなっていません。
配列にとりこまず、セルをループして0値のセルをClearすれば解決するのはわかるのですが、ほかにいい方法はないでしょうか？

Sub TEST0値()
Dim myAr
With ActiveSheet
x = .Range("A" & Rows.Count).End(xlUp).Row
myAr = .Range("A4:AP" & x).Value
For i = LBound(myAr, 1) To UBound(myAr, 1)
For n = LBound(myAr, 2) To UBound(myAr, 2)
If myAr(i, n) = 0 Then myAr(i, n) = ""
Next n
Next i
.Range("A4:AP" & x).Value = myAr
End With
End Sub

回答No.2

myRange · ベストアンサー

こんにちは、エキスパートさん。

０のところには、Emptyを代入してみましょう。

If myAr(i, n) = 0 Then myAr(i, n) = Empty

以上です。

お礼

なんと、= Empty　なんて呪文があったのですね！
存じませんでした。

さすがは大師さまです。
ありがとうございました。

回答No.9

cj_mover

こんにちは
以前、関連した質問に回答していたもので、気になりまして。
宿題の邪魔になってしまうかも知れませんが
やや混乱されてるようにお見受けしたので。
merlionXXさんが以前から気になさっていたのは
たしか、こういうことではなかったかと、、、

Sub test()
With Range("B2")
　　.NumberFormatLocal = "@"

　　.Value = ""
　　MsgBox [istext(b2)]

　　.Value = Empty
　　MsgBox [istext(b2)]

Dim Arr(0)
　　.Value = ""
　　MsgBox [istext(b2)]

　　.Value = Arr
　　MsgBox [istext(b2)]
End With
End Sub

これ、回答じゃなくて、遅ーい、夏のご挨拶ってことで(^^;)

お礼

cj_moverさま、いつもありがとうございます。
な～るほどぉ～っ！

セル書式が文字列になっていると、VBAで　Value = ""　とすると、長さ0の文字列が入って空白にはならないんですね。
その点、Value = Empty　なら、書式に関係なく大丈夫ということですね。

ありがとうございました。
これで安心して夏休み（明日から４連休！蓼科～軽井沢）がとれます！

補足

この質問とはちがいますが、昨年の10月に別件で

> ただ、Value = ""　を使うと、セル書式が文字列になっている場合、完全な空白ではなくなるようで、ISBLANKがFALSEなのにCOUNTBLANKでは空白に数えられるという不整合がおきます。それでClearContentsするようにしています。

なんて、自分で書いているんですね！？
すっかり忘れていました。
その際にもcj_moverさまには大変お世話になり、ありがとうございました。

回答No.8

OtenkiAme

こんにちは。No.3,4,6です。

> Microsoft Scripting Runtime を参照していない環境だと
> Dim myDic As Scripting.Dictionary　がエラーになってしまいました。
処理を実行する前のコンパイルチェックで Scripting.Dictionary オブジェクトが見つからないからです。
ですから「これを使いますよ」って参照設定しておくのです。

> Dim myDic As Object　だとエラーになりません。
処理を実行する前のコンパイルチェックでは、オブジェクトなら何でもいいと宣言しているのでエラーになりません。実行時の参照設定で WorkSheet が入ろうと、Scripting.Dictionary が入ろうともExcelは関知しないのです。
そして、処理が実行され、Set myDic = CreateObject("Scripting.Dictionary") の行にきて初めて "Scripting.Dictionary"オブジェクトを使うと分かり、Microsoft Scripting Runtime ライブラリを探してmyDicに参照設定します。
これが実行時バインディングというものです。その時点で Microsoft Scripting Runtime ライブラリが見つからない時は、エラーになります。
事務所で書類を作成している時に、必要な資料がなくて書庫に探しに行くようなものです。資料がなければ作業を中断せざるを得ません。

> 参照しない場合は Dim myDic As Object　でやるしかないということですね。
そうなります。As Object のことは、CreateObject 関数のヘルプにきちんと書かれています。
ですが、Scripting.Dictionary を使おうとしていて、参照設定できる環境にあるのにあえて As Object と宣言して参照設定のメリットを享受しない理由が判りません。
処理を速くしたいと思ってコードを組んでいるのなら、参照設定してきちんとオブジェクトの型を宣言すべきと私は思います。

お礼

> そして、処理が実行され、Set myDic = CreateObject("Scripting.Dictionary") の行にきて初めて "Scripting.Dictionary"オブジェクトを使うと分かり、Microsoft Scripting Runtime ライブラリを探してmyDicに参照設定します。

なるほど、そういうことだったんですか。
そんなことも知らずに使っていました。
勉強になりました。

今回はわたしの勘違い質問でしたが、ずいぶん多くを学ぶことが出来ました。
OtenkiAmeさん、ありがとうございます。
皆様、ありがとうございます。

回答No.7

myRange

>みなさま大変おさがわせいたしました。

いえ、いえ、かまいませんよ。
お騒がせ大好きです。。。(^^;;;

ただ、ある条件のもとでは質問の件のように
""を代入することで、長さ０の文字列が入る場合があります。
で、当方の回答はそれを考慮しての回答でした。
要するに、未入力の状態にするということです。

さてさて、この【ある条件】とは何でしょう。
ま、これは夏休みの宿題、ということで。。。(^^;;;

以上です。

お礼

【ある条件】？

Sub Test1()
With Range("A1")
.Formula = "="""""
.Copy
.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End With
MsgBox IsEmpty(Range("A1"))
End Sub

ということではなく、配列で入れた場合の話ですよね？
何でしょう？！
夏休み終わってしまいました・・・・。

回答No.6

OtenkiAme

こんにちは。No.3、No.4です。

> これはどのような違いがあるのでしょうか？
> Newがついてるので何か違うのでしょうがわかりません。
Newキーワードは、オブジェクトのインスタンスを実際に作成させるために記述しています。

詳しいことは、VBAのヘルプファイルのCreateObject関数の項目とSetステートメントの項目をご一読いただくと良いかと思いますが、私の場合、もし使用するオブジェクトが分かっているなら、変数の宣言の段階でオブジェクトを宣言するようにしています。

これは、事前バイディングと実行時バインディングの違いによるもので、VBAを使い始めた頃に処理が高速になると教えられました。

例ですと
Dim myDic As Object
は、オブジェクトへの参照が実行されるまで何が入るか分からないですよね。
でも
Dim myDic As Scripting.Dictionary
なら、オブジェクトは明確に宣言されているので、処理が実行される前に変数の最適化が行われたり、コードを作成する時にオートメンバの機能などが使えます。

CreateObject関数を使うなら
Dim myDic As Scripting.Dictionary
Set myDic = CreateObject("Scripting.Dictionary")
として使用したほうが事前に変数が最適化されているので処理が速くなるということです。
高速な処理を目指すなら細かいことですけど、使用する変数は、型を含めてきちんと宣言しておくと良いと思います。
（少なくとも私はそうしています。）

ちなみに、CreateObject関数を使用するか、実際のオブジェクトを参照するかは、好みによるのかな？と思います。
「どちらが最適なのか」までは解説できる知識を持ち合わせていませんのであまり突っ込まないでくださいね。(^_^;)

ではでは。

お礼

なんどもありがとうございます。

> Dim myDic As Scripting.Dictionary
> Set myDic = CreateObject("Scripting.Dictionary")
> として使用したほうが

Microsoft Scripting Runtime を参照していない環境だと
Dim myDic As Scripting.Dictionary　がエラーになってしまいました。
Dim myDic As Object　だとエラーになりません。
参照しない場合はDim myDic As Object　でやるしかないということですね？

回答No.5

Wendy02

んにちは。

>これで見た目には目的を達しているのですが、実際には0値が長さ０の文字列に変わっただけで完全な空白にはなっていません。

良く分からないですね。配列変数のVariant 型で、長さ0の文字列[""]であったとしても、

　.Range("A4:AP" & x).Value = myAr

としたら、[""]をセルに代入したら、空になってしまうのでは？

例えば、Range("A1").Value = "" は、A1 は、空のはずです。
それとも、何か入っているのですか？
もし、セルに[""]を入れるなら、

　Range("A1").Value =　""""""

'-------------------------------------------
Sub EmptyTest1()
Range("A1").Value = ""
If IsEmpty(Range("A1")) Then
　 MsgBox "セルは空です。"
Else
　 MsgBox "何か残っています。"
End If
End Sub
'-------------------------------------------

お礼

わたしは何か、とんでもない勘違いをしていたようです。
計算結果で　=""　になったセルを、コピーして値貼り付けした場合の現象が頭にあって、ためしもせずに質問してしまいました。
試したところWendy02さまのおっしゃる通りでした。

みなさま大変おさがわせいたしました。
ごめんなさい。

回答No.4

OtenkiAme

こんにちは。No.3です。

> 実際は0値のみならずさまざまな条件で空白にしたいので
それならDictionaryを使ってみては如何でしょうか？

'VBEのツール－参照設定で Microsoft Scripting RuntimeをチェックしてＯＫ
Dim myAr As Variant
Dim x As Long, i As Long, n As Long
Dim myDic As Scripting.Dictionary

Set myDic = New Scripting.Dictionary
'辞書にKey（照合値）とItem（Keyと一致したときの要素）を追加
'例えば0,1,3,5を空にする
With myDic
　　.Add 0, Empty
　　.Add 1, Empty
　　.Add 3, Empty
　　.Add 5, Empty
End With

With ActiveSheet
　　x = .Range("A" & .Rows.Count).End(xlUp).Row
　　myAr = .Range("A4:AP" & x).Value
　　For i = LBound(myAr, 1) To UBound(myAr, 1)
　　　　For n = LBound(myAr, 2) To UBound(myAr, 2)
　　　　　　'辞書と照合してItem(ここではEmpty）を代入
　　　　　　If myDic.Exists(myAr(i, n)) Then myAr(i, n) = myDic(myAr(i, n))
　　　　Next n
　　Next i
　　.Range("A4:AP" & x).Value = myAr
End With
Set myDic = Nothing

お礼

ご丁寧にありがとうございます。
Dictionaryを使うことは思いもよりませんでした。
参考までに教えていただければ幸いですが、わたしがこれまで使ったことがあるDictionaryは、

Dim myDic As Object
Set myDic = CreateObject("Scripting.Dictionary")

のような記述ではじめるのですが、

Dim myDic As Scripting.Dictionary
Set myDic = New Scripting.Dictionary

これはどのような違いがあるのでしょうか？
Newがついてるので何か違うのでしょうがわかりません。

回答No.3

OtenkiAme

こんにちは。

置換したら如何でしょうか？

Dim x As Long
With ActiveSheet
　　x = .Range("A" & .Rows.Count).End(xlUp).Row
　　.Range("A4:AP" & x).Replace What:="0", Replacement:="", _
　　　　LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
End With

お礼

ありがとうございます。
たしかに質問の例の通り0値だけが対象ならこれでいけますね。
ただ、実際は0値のみならずさまざまな条件で空白にしたいので配列に取り込んだのです。
言葉足らずでもうしわけありませんでした。

回答No.1

FEX2053

多分コードは組める方だと思うのでヒントだけ。

データを取り込んで、「取り込んだ範囲を".clear"」
あとは、データのある部分だけ書き込み、という方法は如何かと。

お礼

さっそくありがとうございます。
データのある部分だけ書き込み、とはこういうことでしょうか？
これだとやはり個々にセルに入れるので時間がかかってしまいます。

Sub TEST0値2()
Dim myAr
With ActiveSheet
x = .Range("A" & Rows.Count).End(xlUp).Row
myAr = .Range("A4:AP" & x).Value
.Range("A4:AP" & x).ClearContents
For i = LBound(myAr, 1) To UBound(myAr, 1)
For n = LBound(myAr, 2) To UBound(myAr, 2)
If myAr(i, n) <> 0 Then .Range("A4:AP" & x).Cells(i, n) = myAr(i, n)
Next n
Next i
End With
End Sub