エクセルVBA 長さ0の文字列をNullに

Question

エクセル2000です。
アクセスから出力されたデータをエクセルに貼り付けた場合、問題がおきることがあります。
調べてみたら、アクセスでは、同じ空白でも、レコードが作られてからまだ一回もデータが入っていない状態と、何かデータが入ったことはあるが、削除されて今は空白になった状態をそれぞれ「Null値」と、「長さ 0 の文字列」との 2 種類に区別しているようです。
そのためなのか、それをコピーしてくるとエクセル側でも何もデータが入ってないのに「空白」とはみなされないセルができてしまい、マクロの動きをおかしくしてしまうことがあります。（今日、マクロが想定しない動きをして、その原因がわからず往生しました）
やむをえず以下のようなマクロをつくりましたが、Usedrangeが広いとこれもけっこう時間がかかります。

Sub Null化()　'長さ0の文字列をNullに
　　With Application
　　　　.ScreenUpdating = False
　　　　.Calculation = xlCalculationManual
　　　　　　For Each c In ActiveSheet.UsedRange
　　　　　　　　If c.Value = "" And Not IsNull(c) Then
　　　　　　　　　　c.ClearContents
　　　　　　　　End If
　　　　　　Next c
　　　　.Calculation = xlCalculationAutomatic
　　　　.ScreenUpdating = True
　　End With
End Sub

最初から、「長さ 0 の文字列」セルを一度に選択する方法があれば簡単なのですが、そのような方法はありますか？
あるいは他のもっとよい方法などがあればご教示くださいませ。
　(o。_。)oペコッ

end-u · Accepted Answer

>それをコピーしてくると...
とは？どんな手順ですか？
もし単純なコピーペーストだったら、それを[形式を選択して貼り付け]-[テキスト]で貼り付けても一緒でしょうか？


いずれにしても『'長さ0の文字列をNullに』する処理の別案。

【Ａ】
With ActiveSheet.UsedRange
　　.Value = .Value
End With
（注意）
・データ範囲が多すぎると遅い。（もしくはメモリ不足エラーになる可能性も）
・１セル内文字列が1,824文字以上だと切り捨てられる。（2003の場合は912文字以上でエラー）
・セル表示形式が標準の場合、データによっては型が変わる。（'0123が123に...表示形式設定が必要）

【Ｂ】
Dim x
With ActiveSheet.UsedRange
　　For Each x In Array(1, 3, 6)
　　　　.Columns(x).TextToColumns DataType:=xlDelimited, _
　　　　　　　　　　　　　　　　　TextQualifier:=xlDoubleQuote, _
　　　　　　　　　　　　　　　　　ConsecutiveDelimiter:=False, _
　　　　　　　　　　　　　　　　　Tab:=False, _
　　　　　　　　　　　　　　　　　Semicolon:=False, _
　　　　　　　　　　　　　　　　　Comma:=False, _
　　　　　　　　　　　　　　　　　Space:=False, _
　　　　　　　　　　　　　　　　　Other:=False, _
　　　　　　　　　　　　　　　　　FieldInfo:=Array(1, 2)
　　Next
End With
列単位でTextToColumnsメソッドを使う。（[データ]-[区切り位置]のことです）
必要な列のみ処理でき、引数FieldInfoでデータ形式も設定できます。

【Ｃ】
それと、別インスタンスでExcelを起動し、（Excel.exeを２つ起動する意味）
別エクセルに[形式を選択して貼り付け]-[テキスト]で貼り付ける事でも対処できるかと。
この場合1,824文字以上の切捨てはありません。（表示形式に留意必要）
必要であればマクロ対応もできるでしょう。

end-u · Answer

>【Ｂ】のFor Each x In Array(1, 3, 6)の１、３、６は何をさすのでしょうか？
>これもそのままやってみしましたがEmptyにはなりませんでした。
あれ？そうですか。
まあ、いいです。そういう事もあるのかもしれません。
とりあえず『１、３、６は』.Columns(x)...という記述から推測できると思いますが
UsedRangeの列を指します。
対象列が限定されている場合などに使えるかな...程度で。
書き忘れてましたが、こちらは255文字で切り捨てられるんでした。（...使えんorz）

次に【Ｃ】の質疑がくるかもしれませんが、色々と盛りだくさんにしてもあれなのでそれはパスという事で。

#ちなみにcj_moverさんのSub NLS_Sweeper()は通常は問題なく機能します。
>VBEでは、.Range(sURAd).Formula = sFml　の部分が黄色くなっていました。
問題はシート名のほうかと。その場合
"=1/(ISTEXT('" & .Name & "'!RC)*('" & .Name & "'!RC=""""))"
シングルクォーテーションでくくってあげれば良いです。

Wendy02 · Answer

こんにちは。

>こんなセルがあるとEnd(xlUp)などで最終行を取得したい場合や、ワークシート関数でISBLANKやCOUNTAでは空白でないとされるのにCOUNTBLANKでは空白扱いされて、こまったことになってしまうのです。

既出の回答で、 .Value = .Value すでに解決策は出ているようですし、私自身は、申し訳ないけれども、テクニカル上のものは、ここで、あまりあれこれと出すつもりはありません。たぶん、間違いないという確信はあるものの、実際にやってみないと分からないです。

たぶん、「大山鳴動して鼠一匹」というところだと思います。

もともと、AccessからExcelへのインポート上の問題だろうし、実際に、何が入っているか、エスケープシーケンスなのか、NullCharなのか、それとも他のものなのか明らかになっていないし、基本的には、数式が入っているわけはないだろうし、入っているものが文字なら、一括置換でクリアになるはずです。

>End(xlUp)などで最終行を取得したい場合

Unicode 空白でも、エスケープシーケンスでも、何が入っているかは明らかにしたほうがよいのでは？複数入っていることもあるだろうから、

If c.Value = "" And Not IsEmpty(c) Then
この後で、AscW(c) で、調べたらよいと思います。また、Byte で調べても可能です。

ただし、JISのAsc(c) ですと、一律同じ文字コードになってしまう可能性があります。それが、どのような文字コードなのか、ヒットしたコードを、置換して空にすればよいと思います。

それなら、End プロパティで取れるようになれます。しかし、そうしなくても、おそらくは、Match関数で最終行は取れるはずです。

cj_mover · Answer

すみません。
＃４の投稿が途切れてしまったので、再掲します。

Sub NLS_Sweeper()
Dim sh0 As Worksheet
Dim sFml As String, sURAd As String
　With Application
　　.EnableEvents = False
　　.Calculation = xlCalculationManual
　　.ScreenUpdating = False
　　.DisplayAlerts = False
　End With
　Set sh0 = ActiveSheet '任意指定
　With sh0
　　sURAd = .UsedRange.Address
　　sFml = "=1/(ISTEXT(" & .Name & "!RC)*(" & .Name & "!RC=""""))"
　End With
　With Worksheets.Add(sh0)
　　.Range(sURAd).Formula = sFml
　　sh0.Select False
　　　On Error GoTo ln9
　　Cells.SpecialCells(xlCellTypeFormulas, xlNumbers).Select
　　Selection.ClearContents '任意
ln9:　If Err.Number > 0 Then MsgBox "NLS_Areas_(Over_8192)_or_0"
　　　On Error GoTo 0
　　.Delete
　End With
　Set sh0 = Nothing
　With Application
　　.EnableEvents = True
　　.Calculation = xlCalculationAutomatic
　　.ScreenUpdating = True
　　.DisplayAlerts = True
　End With
End Sub

cj_mover · Answer

こんにちは。
よく解りませんが、長さ０の文字列、お嫌いなようですね。
「Excelにやらせる」タイプのマクロ（つまり手作業で可）
を一つのアイディアとして掲げてみます。
・.SpecialCells は領域数8192が上限です
・標準モジュール専用、自ブック専用、です
・他、特殊な状況は想定しません

課題からやや外れて、
表示形式＝文字列に設定されたセルでも
長さ０の文字列を消せる
作業手順をコード化しました。

Sub NLS_Sweeper()
Dim sh0 As Worksheet
Dim sFml As String, sURAd As String
　With Application
　　.EnableEvents = False
　　.Calculation = xlCalculationManual
　　.ScreenUpdating = False
　　.DisplayAlerts = False
　End With
　Set sh0 = ActiveSheet '任意指定
　With sh0
　　sURAd = .UsedRange.Address
　　sFml = "=1/(ISTEXT(" & .Name & "!RC)*(" & .Name & "!RC=""""))"
　End With
　With Worksheets.Add(sh0)
　　.Range(sURAd).Formula = sFml
　　sh0.Select False
　　　On Error GoTo ln9
　　Cells.SpecialCells(xlCellTypeFormulas, xlNumbers).Select
　　Selection.ClearContents '任意
ln9:　If Err.Number > 0 The

Wendy02 · Answer

こんばんは。

何もないのに、UsedRange やSpecialCells(xlCellTypeLastCell)で、最後尾のデータを探すと、空のセルを示していることがあります。これ自体は、Excelのマクロの最後尾を探すコードの問題で、それ以上の問題ではないはずです。

もし、削除したいのでしたら、列や行の削除になるはずですが、保存して、再起動して開ければなくなっているはずです。これは、セルを使った痕跡だと思います。そのままの状態では、無視するしかありません。ただし、書式が残っている場合は、それを消さないと最後尾は探せません。

再起動せずに、最後尾を探す場合は、マクロでは、End プロパティを使います。

ワークシート上のNull（本来の意味は、ゼロ） は、Excelでは何もないという意味ではなくて、エラー値のひとつで、指定した複数のセル範囲に共通部分がない時に出ます。VBAのNull 値は、バリアント型 (Variant) に有効なデータが入っていないことを示す値であり、変数が初期化されていないことを示しますが、Empty 値や、「Null 文字列」と呼ばれる「長さ0の文字列 ("")」 とは異なります。

ワークシートで、何もないのは、Empty です。検査する場合は、IsEmpty(c) です。「=""」Null文字を判定する場合は、If c.Value ="" And VarType(c) = vbString です。だから、ワークシートで、VBAのIsNull の判定は役に立ちません。

=AVERAGE( B1:B5 C2:C6 )　　-->#NULL! (Nullエラー値）

>If c.Value = "" And Not IsNull(c) Then　

ワークシートに該当する、VBAに該当するNull値は存在していません。

hornest · Answer

DBにはNULLが入ってるものです。
VBにはNULLの概念が無いので、エラーが起きてしまいます。

なのでどちらにせよ処理しなければいけないのですが、通常であれば
データを取得した際にCutNULL()としておくべきでしょう。

Excel上で処理をすると重いのは当然です。

エクセルVBA 長さ0の文字列をNullに

>それをコピーしてくると...

>【Ｂ】のFor Each x In Array(1, 3, 6)の１、３、６は何をさすのでしょうか？

こんにちは。

すみません。

この回答への補足

こんにちは。

こんばんは。

DBにはNULLが入ってるものです。

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング