関数の結果を""ではなく設定されていないセルにしたい

質問

EXCEL2000を使用してます。Sheet1シートのA列のセルには、値（文字数値）が設定してあって、B列のセルで以下のような関数を設定してあります。
たとえばB10セルの場合、
=IF(A10="","",T("2"&TEXT($B10,"0000"))
A列に何も値がない場合、B列には何も表示されないのですが、Sheet1をSheet2へ「形式を選択して貼付け」→「値」をした場合、B列に""が結果として設定されたセルに何かの値が残っているようです。（A列は改行等の制御文字はありません。）CSVファイルに出力してテキストエディタで参照すると半角スペースのように見えます。貼付け時に「空白を無視する」をチェックしても何かの値がセルに残っているようです。Sheet2にコピーしたときに関数で設定されたセルを何も入力されていないセルにする方法をご教授願います。

回答No.5

Wendy02 · ベストアンサー

こんにちは。

>型が一致しません。

こちらでは、想定していないデータがシートの中にあったようです。
もともと、コピー－値貼り付けで考えていたものですから、概ね、文字列を対象と考えていたからです。原因は分かりましたが……。それは、本当に想定外です。

コードを以下のように書き換えてみました。

　　'ワークシート全部を行います。
　　For i = 1 To Worksheets.Count
これは、シートタブの左から、1～シートの数までという意味です。
だから、もし、2番目なら、i =2 になりますし、また、最後まででない場合は、
Worksheets.Count のところを、20シートあれば、17　とか　18　とかすれば、残りは実行せずに、そこまででとまります。

.UsedRange.Cells
これは、データのある部分全体を示していますが、もし、違う場合は、範囲を限定したほうがよいと思います。

　.Range("A1:Z300").Cells

と書き換えることが可能です。.Cells は、他のトラブルを想定して、念のために入れています。必ず「.（コンマ）」は忘れないでください。

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

Sub StringZeroClear2()
　　'長さ0の文字列を消す(改訂版）
　　Dim c As Variant
　　Dim i As Integer
　　Application.ScreenUpdating = False
　　'ワークシート全部を行います。
　　For i = 1 To Worksheets.Count
　　　　With Worksheets(i)
　　　　For Each c In .UsedRange.Cells
　　　　　　'対象を文字列に限定
　　　　　　If VarType(c.Value) = vbString Then
　　　　　　　　'数式を省き、長さ0の文字列を消去する
　　　　　　　　If c.HasFormula = False And Len(c.Value) = 0 Then
　　　　　　　　　　c.ClearContents
　　　　　　　　End If
　　　　　　End If
　　　　Next c
　　　　End With
　　Next i
　　Application.ScreenUpdating = True
End Sub

お礼

ご回答いただき本当にありがとうございます。

出来ました！！

すごい、すばらしい。
途方にくれていた事象だったので感激です。
事象の原因、分析からVBAでの対応策の提供まで本当にありがとうございました。

ご回答いただいた皆様につきましても本当にありがとうございました。

回答No.4

noname#52504

Sheet2にいったん値貼り付けした後で…

★文字列データ中の「長さ0の文字列」を削除する

１．対象範囲を選択
２．編集＞置換
　　　検索する文字列：何も入力しない
　　　置換後の文字列：♂
３．編集＞置換
　　　検索する文字列：♂
　　　置換後の文字列：何も入力しない

Excel2000で挙動確認済。
-------------------------------------------------------------------
※蛇足

以下、「データがない状態」を仮に「ブランク」と表記します。

数値データ中の「長さ0の文字列」を削除するのであれば、
ジャンプ機能で[文字]を選択して削除するのがおそらく最短ですが、
文字列データ中の「長さ0の文字列」の場合は、
ジャンプ機能で切り分けることはできませんし、
置換機能で「長さ0の文字列」⇒「ブランク」を直接処理することもできません。

ただ、
・「長さ0の文字列」or「ブランク」⇒「長さ1以上の文字列」の置換
・「長さ1以上の文字列」⇒「ブランク」の置換
は可能なので、
データ中に登場しない文字や文字列、例えば♂を使って、
間接的に「長さ0の文字列」⇒「ブランク」の置換を行うことができます。

ちなみに、Excel2002以降であれば、
検索機能に「すべて検索」というオプションがあるので、
「長さ0の文字列」or「ブランク」をまとめて検索して、
結果を全て選択、一括削除することができます。

また、作業の流れにもよりますが、もともとの数式を、
　=IF(A10="",NA(),T("2"&TEXT($B10,"0000"))
などとして「長さ0の文字列」の代わりにエラー値を返すようにしておけば、
Sheet2に貼り付けた際にジャンプ機能で切り分けて削除することができます。

以上ご参考まで。長乱文陳謝。

お礼

更なるご回答本当に感謝感謝です。

人力での置換も視野にしていますが
Excelのブックが１０くらいでそのブックの中はシートが２０くらいあるので少しげんなりしています。

回答No.3

koko88okok

ANo.1です。

> 質問させていただいた内容はどうしても解決したい事象です。

スマートな方法は、ANo.2の方のVBAで処理して頂くとして、私の場合は、

「Ctrl+G」で、「ジャンプ」を起動→「セル選択」→「定数」をオンにし、「文字」にチェックを入れて「OK」で、文字列が入力されているセルがすべて選択されます。

ツールバーの「塗りつぶし」を押してセルに色を着けます。

後は、空白のセルを目視でひたすら「セルを選択」→「削除」するだけです。（数が少なければ、考えている間に処理ができますので・・・）

回答No.2

Wendy02

こんばんは。

まさかと思って、ISBLANK関数で調べてみたら、FALSE が出てきます。
これは、VBAでみると、「長さ0文字列（"")」の文字列が入っています。

試してみると、検索は出来ても置換は出来ないようです。

ジャンプの空白セルも利かないわけです。

本来、T("2"&TEXT($B10,"0000"))が、数値でしたら、「""」はいずれにしても文字列ですから、文字列と数値で区分けは出来ますが、両方とも文字列ですから出来ません。

条件付き書式で、
=AND(A1="",ISTEXT(A1))

オートフィル・ドラッグコピー

あたりで、セルのパターンで色を塗ってひとつずつ消去するか、もしくは、VBAの領域になるのではないでしょうか？

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

'標準モジュール

Sub StringZeroClear()
'長さ0の文字列を消す
Dim c As Range
　Application.ScreenUpdating = False
　For Each c In ActiveSheet.UsedRange
　 If c.HasFormula = False And c.Value = "" Then
　　　c.ClearContents
　 End If
　Next c
　Application.ScreenUpdating = True
End Sub

補足

早速の回答ありがとうございます。
標準モジュールを実行してみました。

EXCELメニュー→ツール→マクロ→Visual Basic Editerの順に選択して、
VBAProject(book1.xls)をＷクリックして、ご提供いただいた標準モジュール以下を貼り付けて実行したところ

Microsoft Visual Basicのダイアログに
実行時エラー'13':
型が一致しません。
と表示され、置換されません。

事象として発生しているシートはA列からDJ列まで入力項目があって、
セルが""が設定されていたり、何も設定されていなかったりと行毎に設定がバラバラなシートです。

ご教授いただいた内容は、
参照しているシートのセルの内容が数式やセルの内容が""の場合は、セルの内容をクリアして、参照しているシートの入力されているセル全てが完了するまで繰り返すモジュールと思います。

ダイアログが表示されたので
データ件数を2000件にして以下のモジュールを実行してみましたが上記同様のダイアログが表示されます。
もともとの質問とは違いますが事象解決のためもう少しVBAの作法等も含めてご教授いただけると助かります。
ムリばかりで申し訳ないのですが何卒よろしくお願いいたします。

回答No.1

koko88okok

興味を持ったので試してみました。

> B列に""が結果として設定されたセルに何かの値が残っているようです。

文字情報が残っていました。

確認方法：「Ctrl+G」で「ジャンプ」→「セル選択」→「定数」をオンにして「文字」だけにチェックを入れる→「OK」で、「""」によって空白にしていたセルも選択されました。

> CSVファイルに出力してテキストエディタで参照すると半角スペースのように見えます。

文字の区切りだけ(「,」)が表示されました。

> 貼付け時に「空白を無視する」をチェックしても何かの値がセルに残っているようです。

コピー元の空白セルを貼り付けしない（貼り付け先のデータを残す）機能なので、今回は無効と思います。

この現象を無視できないのであれば、「ジャンプ」→「セル選択」でセルに色を着けておいて、目視で削除ぐらいしか・・・

お礼

早速のご回答感謝感謝です。
質問させていただいた内容はどうしても解決したい事象です。

もし可能であれば、申し訳ないのですが引き続きご教授願います。