Java vbnullstring,使用查找/替换清除vbNullString

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.

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

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

However,

=ISBLANK(A1)

returns 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.

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

IF Cell.VALUE = "" Then

Cell.Clear

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 = ""

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

解决方案

Try this

With ActiveSheet.UsedRange

.NumberFormat = "General"

.Value = .Value

End With