如何使用VBA在Excel中删除零长度的字符串？

Question

如何使用VBA在Excel中删除零长度的字符串？

3

我已经在Excel的VBA中创建了一个工具，可以根据多个标准分析我们收到的.xlsx文件。其中之一是数据集中空单元格的数量。然而，不幸的是，我注意到我们收到的一些文件中包含长度为零的字符串，这些字符串被“错误地”计算为非空单元格。

如果我能够将它们从文件中删除，对整个过程实际上会有很大的好处。

我已经搜索了很多关于此问题的解决方案，但到目前为止，我唯一找到的解决方案是遍历工作表中的所有单元格（我也尝试过仅使用常量，以及使用查找来查找所有zls）。这并不是很有效，因为工作表中有大量的数据。编辑：我还尝试过UsedRange.values = UsedRange.values方法，但这会删除前导零，而我需要保留。

我还发现以下方法可行（-@-是一个随机字符串，在我的数据中极不可能是一个单独的单元格，如果存在，可以被删除）：

ws.UsedRange.Replace what:=vbNullString, replacement:="-@-", _
                            lookat:=xlWhole, MatchCase:=False
ws.UsedRange.Replace what:="-@-", replacement:="", _
                            lookat:=xlWhole, MatchCase:=False

但如果我只使用单个替换，它就无法实现：

ws.UsedRange.Replace what:=vbNullString, replacement:="", _
                            lookat:=xlWhole, MatchCase:=False

第一种方法可以，但存在以下几个问题：

需要的时间是单次替换的两倍
无法确定它是否已崩溃或继续工作
如果它崩溃了，我就会留下一些包含“-@-”的单元格，这并不总是明显的。而且这个工具应该适用于那些不能理解VBA的人。

所以我的问题是：

有没有办法只使用单次替换来完成这个操作？为什么单次替换不起作用，而双重替换可以？
如果第一个问题无法解决，那么是否有一种方法可以在代码崩溃时“回滚”替换？
有没有一种更新状态栏的方法来显示替换的进度，以证明代码正在运行（就像在Excel中运行替换一样）？
还是有更好的方法来完成所有这些操作？

提前感谢您！

编辑：由于处理的数据，我需要保留格式，包括前导零

编辑：这里有一个我正在查看的数据示例。我想要将零长度字符串（即非空单元格但其中没有值）替换为真正的空单元格。

- Gemma Down

@FoxfireAndBurnsAndBurns 这就是我遇到问题的原因。它们不是空单元格，所以它们不被视为空单元格，但实际上它们应该是空的。因此，我想把它们删除。它们都包含零长度字符串，而不是空白或空的。 - Gemma Down

哦，所以所有那些烦人的单元格都包含公式吗？你能否只是用 "" 替换掉所有包含公式的单元格？或者你有一些包含公式但不能删除的重要信息吗？ - Foxfire And Burns And Burns

它们确切地位于您的表格的哪里？这些是底部行还是最右侧列？ - Luuklag

你的 Excel 版本中是否有 PowerQuery？（使用 PQ，您可以一次性将空字符串替换为 null） - EvR

你不能直接上传到SO，你必须使用任何外部托管服务并添加链接。我知道文件可能不同，但请创建一个可以轻松显示你所面临的问题 - 长时间/崩溃替换的文件。 - BrakNicku

显示剩余12条评论

3个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Gary's Student · Answer 1

您可以拥有返回空字符串的公式单元格或带有空字符串常量的单元格。要清除第二种类型的单元格：

Sub KillNullConstants()
    Dim cell As Range, Konstants As Range, rng As Range
    Set Konstants = ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeConstants)
    Set rng = Nothing
    For Each cell In Konstants
        If Len(cell) = 0 Then
            If rng Is Nothing Then
                Set rng = cell
            Else
                Set rng = Union(rng, cell)
            End If
        End If
    Next cell

    If Not rng Is Nothing Then
        rng.ClearContents
    End If
End Sub

清除返回空字符串的公式单元格，只需更改SpecialCells代码行即可。

- JvdV · Answer 2

我知道这也是一个循环，但也许有更快的方式：

之前：

在示例数据上运行此代码：

Dim X As Double

Option Explicit

Sub Test()

Application.ScreenUpdating = False
Application.Calculation = xlManual

With ActiveWorkbook.Sheets(1).Range("A1:C7")
    For X = 1 To 3
        .AutoFilter Field:=X, Criteria1:=""
        .Columns(X).Offset(1, 0).SpecialCells(xlCellTypeVisible).Clear
    Next X
    .AutoFilter
End With

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

End Sub

之后：

- barrowc · Answer 3

将工作表读入 ADO Recordset，然后将 Recordset 复制到新工作表似乎可以解决此问题。请尝试以下 VBA 代码：

Sub copy_data()

Dim cn As Object
Set cn = CreateObject("ADODB.Connection")

With cn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
        "Extended Properties=""Excel 12.0 Macro;IMEX=1;HDR=YES"";"
    .Open
End With

Dim rs As Object
Set rs = CreateObject("ADODB.Recordset")

rs.Open "SELECT * FROM [Sheet1$];", cn

Dim i As Integer
Dim fld As Object

With ThisWorkbook.Worksheets("Sheet2")
    .UsedRange.ClearContents

    i = 0
    For Each fld In rs.Fields
        i = i + 1
        .Cells(1, i).Value = fld.Name
    Next fld

    .Cells(2, 1).CopyFromRecordset rs
    .UsedRange.Columns.AutoFit
End With

rs.Close
cn.Close

End Sub

注意：

更改代码中的工作表名称以匹配您使用的工作表名称。在rs.Open中使用工作表名称后面必须加上$符号。如果您的工作表名为"Data"，则应该写成rs.Open "SELECT * FROM [Data$];", cn
在连接字符串的扩展属性中，使用HDR=YES来指定数据具有标题。ADO可能会更改某些列名，如果它们包含某些字符-主要是.字符，通常会被替换为#字符

潜在问题：

您的数据需要大致呈现表格格式-即第1行的列名及其下方的数据值
数据中的任何公式将转换为数值
不会复制单元格格式-但是以文本格式进行格式化的数字将保持为文本，因此前导零将被保留
任何超过255个字符的单元格值文本可能会被截断为255个字符（如果需要，可以解决此问题）

根据您的数据，使用ADO可能会带来更多问题，而不是解决问题。