エクセル（2003） 「1900/1/0」を消したい

質問

セルA1に0を入力し
書式設定で日付→yyyy/m/dを選択しました。

しかし下記のマクロ実行しても
0が空白になりません。

Sub 日付置換()
Cells.Replace what:="1900/1/0", Replacement:="", LookAt:=xlPart
Cells.Replace what:="00/01/00", Replacement:="", LookAt:=xlPart
Cells.Replace what:="0", Replacement:="", LookAt:=xlWhole
End Sub

なら最初から0なんか入力しなきゃいいじゃん。
と思われるかもしれませんが
これはCSVファイルで出力したもので
最初から0の数値にも書式設定がかかってて「1900/1/0」となっています。

「1900/1/0」のデータをなくしたいのですが
どんな方法がありますか？
データ量が多いためマクロなどで一度に行ないたいです。

アドバイスよろしくお願いします。

回答No.4

mshr1962 · ベストアンサー

>セルA1に0を入力し
>書式設定で日付→yyyy/m/dを選択しました。
単純ですが
書式設定をユーザー定義で→yyyy/m/d;;　としてみてください。
0の時の表示が1900/1/0からブランクに変わります。

お礼

こんな方法があるのですか。
初めて知りました。
参考になりました。
ありがとうございます。

「;;」を付ける事によって、
「0なら空白」という意味になるのでしょうか？

回答No.9

Wendy02

こんにちは。
#5の回答者です。

少し、読み直し、いろいろ考えてみました。
>これはCSVファイルで出力したもので
>最初から0の数値にも書式設定がかかってて「1900/1/0」となっています。

たぶん、マクロか何かでインポートしているのでしょうか？
書式設定が掛かっているかは別として、型キャストが起きていれば正しい日付値になっているはずです。しかし、「1900/1/0」が存在していますから、どうやらワークシート上では、日付値にもなっていないはずです。つまり、型キャスト（該当する書式で値が変更)が、おきていないようです。

テキストのソースのまま、処理しても可能だと思うのですが、CSVは、列に対しては、取り扱いが煩雑になります。だから、ワークシートにインポートし、もう一度、区切り位置で処理すれば、ワークシート上なら、日付書式にならないものは、はじき出されて、文字列になるはずです。それは、手動でも、そんなに大変ではないはずです。

ご質問の要求が、マクロ・コードを書かれていますから、あえてマクロ化しました。

「0」自体は、そのままなら、置換できるはずですから、その部分のコードは書いていません。

こちらの書いた、マクロについては評価しなくても構わないのですが、Excelの持つ機能からの操作は、マクロを使わなくても分かるはずです。ご質問者さんの技術の理解度とは別に、数値と日付値の区分け程度は、VBAで求めた方が良いのではないかなって思います。

'-------------------------------------------

Sub FindStrings()
　　Dim rng As Range
　　Dim col As Range
　　Dim r As Range
　　With ActiveSheet
　　　　Set rng = .Columns("A:B") 'インポートされた範囲
　　　　On Error Resume Next
　　　　For Each col In rng.Columns
　　　　　　If WorksheetFunction.Count(col) > 1 Then
　　　　　　　　col.TextToColumns _
　　　　　　　　Destination:=col.Cells(1, 1), _
　　　　　　　　FieldInfo:=Array(1, 5)
　　　　　　　　Set r = col.SpecialCells(xlCellTypeConstants, xlTextValues)
　　　　　　　　If Not r Is Nothing Then
　　　　　　　　　　r.ClearContents　'書式はクリアしない・値だけ
　　　　　　　　End If
　　　　　 End If
　　　　 Next col
　　　　On Error GoTo 0
　　End With
　　Set rng = Nothing
End Sub

'-------------------------------------------

お礼

コードの意味は難しいですができました！
ありがとうございます。

回答No.8

myRange

またまたまた登場、myRangeです。

＞最初に書式を"G/標準"にするのはなぜなのでしょうか？

書式を日付（yyyy/m/d）にしておくと、
Replaceでは実際の値である０を捉えられないようなので
Replaceで捉えられるように実際の値０をそのまま表示させておくためです。

こんなんで説明になってますか？
以上ここまで。

お礼

何度もありがとうございます。
Replaceは奥が深いですね。
参考になりました。

回答No.7

myRange

またまた登場、myrangeです。
恥ずかしながら回答６にポカあり。

回答６でReplaceを関数と連呼してますが
正しくは、Replaceメソッドです。。。(^^;;;

以上ここまで。

お礼

そうなんですか！
まったく気づきませんでした！

回答No.6

myRange

回答３、myrangeです。
上手くいったようで何より。。

＞一度、"G/標準"にすることにより、0は文字列と認識されて
＞Replaceで置換できるのでしょうか？

０は文字列ではなく数値です。
どうもReplace関数について勘違いがあるようです。

●Replace関数は、文字列、数値、どちらでも置換可能●

新しいシートで以下を試してみれば分かるでしょう。

Ａ１とＢ１に、０
Ａ２とＢ２に、１００
Ａ３とＢ３に、１０２０

を入力しておき次を実行

Sub Test()
Range("A:A").Replace What:="0", Replacement:="", LookAt:=xlPart
Range("B:B").Replace What:="0", Replacement:="", LookAt:=xlWhole
End Sub

どうでしょう、０は置換されてるはずです。
これで数値も置換されるということが分かりますね。

また、Ａ列とＢ列の結果に違いがあることも気付いた思います
これは、引数LookAtの値(xlPart,xlWhole)の違いによります。

質問の件では回答３で示したとおり、xlWholeを使わなければいけません。
理由は分かりますよね。

Replace関数のように引数のある関数を理解するときは
引数の値を変えながら色々試してみることをお勧めします。

以上ここまで。

お礼

再度ご回答ありがとうございます。
Replace関数は数値でも文字列でも使えるのですね。

まだよく理解していない為わからないのですが
最初に書式を"G/標準"にするのはなぜなのでしょうか？
質問ばかりですいません、、、

回答No.5

Wendy02

こんばんは。

この出来ないのは、Excel 2000 での仕様だと思っていました。上位バージョンでもメニューから置換する方法は出来るのですが、VBAからはできません。当然と思う方もいるようですが、私は、これは、隠れたバグのようなトラブルのひとつだと思っています。

ただし、置換する方法もあるのですが、ものすごく難しいテクニックが必要になってしまいます。

以下は、数値の1 は、"1900/1/1"　と区分けします。
書式は、そのままで行います。

'--------------------------------------------------------
Sub　Tes1()
　　Dim r As Range
　　Dim c As Range
　　Const sD1 = "1900/1/1"
　　Const sD2 = "1900/1/0"
　　Const sD3 = "0"
　　With ActiveSheet
　　　　On Error Resume Next
　　　　Set r = .UsedRange.SpecialCells(xlCellTypeConstants, 23)
　　　　If r Is Nothing Then MsgBox "検索対象の数値がありません。", vbExclamation: Exit Sub
　　　　On Error GoTo 0
　　　　For Each c In r
　　　　　　If StrComp(sD1, c.Text) * StrComp(sD2, c.Text) * StrComp(sD3, c.Text) = 0 Then
　　　　　　　　c.ClearContents
　　　　　　End If
　　　　Next c　　
　　End With
　　Set r = Nothing
End Sub

'--------------------------------------------------------

お礼

ご回答ありがとうございます。なんだかよくわかりませんができました。

回答No.3

myRange

方法はいくつかあると思いますが、一案。

　Ａ列を対象とした場合。

（１）Ａ列の書式を標準にする
（２）０を変換する
（３）Ａ列の書式を、yyyy/m/d　に戻す

'-------------------------------------
Sub 日付置換()
Range("A:A").NumberFormatLocal = "G/標準"
Range("A:A").Replace What:="0", Replacement:="", LookAt:=xlWhole
Range("A:A").NumberFormatLocal = "yyyy/m/d"
End Sub
'-------------------------------------

以上ここまで。

お礼

できました。
ありがとうございます。

一度、"G/標準"にすることにより、0は文字列と認識されて
Replaceで置換できるのでしょうか？

回答No.2

fujillin

Replaceは文字に対して有効です。

例えば、102345という「数字」が入っているセルに、ご質問のReplaceを実行しても、0が無くなって12345にはなりませんよね？
日付はエクセルでは特殊なデータですが、どちらかと言えば数字に近いもので、文字ではありません。（計算が可能）

手っ取り早いのは、
「ツール」-「オプション」-「表示」で「ゼロ値」のチェックをはずせば0が表示されなくなります。（2003の場合）
ただし、この方法だと、他のセルに（表示が必要な）数字の0が入っていても表示されなくなってしまいます。

マクロでやる場合は、面倒ですが、各セルの値（または表示文字）をチェックして置き換えてあげる必要がありそうです。
先に、対象になるセルの範囲が限定できれば
　For Each Cell In Range
などの方法でループするのがよろしいでしょう。

その範囲内に、普通の数字0（←こちらは消したくない）が含まれているような場合は、表示書式と値の両方をチェックするか、または、表示テキストが
1900/1/0のものを決め打ちで空白にすればよいのでは？

お礼

Replaceが文字だけとは初めて知りました！
ありがとうございます。

回答No.1

hana-hana3

こんな感じでいかがでしょうか？

Sub zero()

Dim c As Range

For Each c In Range("A1:Z999")
If c.Value = 0 Then c.Value = ""
Next

End Sub

お礼

できました！
0なら""にしているのですね！ありがとうございます。