使用查找/替换清除vbNullString [英] Use Find/Replace to clear vbNullString

问题描述

我有一个电子表格，该电子表格在企业系统中作为报告生成，并下载到Excel电子表格中.即使没有数据存在，结果电子表格中的空白单元格也不是真正空白-并且空白单元格不包含空格"字符.

I have a spreadsheet that is generated as a report in our Enterprise system and downloaded into an Excel spreadsheet. Blank cells in the resulting spreadsheet are not really blank, even though no data is present - and the blank cells do Not contain a 'space' character.

例如，A2中的以下单元格公式返回TRUE(如果A1是空白单元格):

For example, the following cell formula in A2 returns TRUE (if A1 is a blank cell):

=IF(A1="","TRUE","FALSE")

但是，

=ISBLANK(A1)

返回FALSE.

您可以通过在单元格中键入撇号(')并复制该单元格来复制此问题.然后，使用选择性粘贴..."值粘贴到另一个单元格，并且在粘贴的单元格或公式栏"中都看不到撇号.似乎有一个清晰的单元格，但它将使用ISBLANK评估为FALSE. 当需要将虚假的空白单元格放在排序的底部时，这会导致其排序，从而导致虚假的空白单元格位于升序的顶部.

You can replicate this problem by typing an apostrophe (') in a cell and copying the cell. Then, use Paste Special...Values to paste to another cell and the apostrophe is not visible in the pasted cell, nor in the Formula Bar. There appears to be a clear cell, but it will evaluate to FALSE using ISBLANK. This causes sorting to result in the fake blank cells at the top of an ascending sort, when they need to be at the bottom of the sort.

我可以使用vba循环修复伪造的空白，遍历每一列并进行评估

I can use a vba loop to fix the fake blanks, to loop through every column and evaluate

IF Cell.VALUE = "" Then
   Cell.Clear

但是因为电子表格具有成千上万的数据行和多达50列，所以这增加了程序的大量开销，我更喜欢使用FIND和Replace.

but because the spreadsheet has tens of thousands of rows of data and as many as 50 columns, this adds substantial overhead to the program and I would prefer to use FIND and Replace.

以下是当前不起作用的代码:

Here is the code that does not currently work:

Range("ZZ1").Copy
Range("Table1[#All]").Select
With Selection
   .Replace What:="", Replacement:=.PasteSpecial(xlPasteValues, xlNone, False, False), _ 
   LookAt:=xlWhole, SearchOrder:=xlByColumns, MatchCase:=False, SearchFormat:=False, _
   ReplaceFormat:=False
End With

以下操作也无法清除伪造的空白单元格:

The following things do not work to clear the fake blank cells either:

Replacement:= vbnullstring
Replacement:= ""
Replacement:= Cells.Clear
Replacement:= Cells.ClearContents
Replacement:= Cells.Value = ""

我尝试了20种其他方法，这些方法也不起作用.

I have tried 20 other things that do not work either.

推荐答案

尝试一下

With ActiveSheet.UsedRange
    .NumberFormat = "General"
    .Value = .Value
End With

这篇关于使用查找/替换清除vbNullString的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！