vba - 删除单列中的空格

Question

我有一张工作表，我需要从中删除空格，以便我可以比较它，我正在使用我在此处找到的一个函数和一个将所有值放入数组中的子程序(为了速度)，但我无法将其知道为什么。我得到一个

ByRef argument type mismatch error

Public Function RemoveWhiteSpace(target As String) As String
    With New RegExp
        .Pattern = "\s"
        .MultiLine = True
        .Global = True
        RemoveWhiteSpace = .Replace(target, vbNullString)
    End With
End Function


Sub stringRangeToClean()

Dim r As Variant
Dim i As Long

r = ActiveWorkbook.Sheets("Trent BASE DATA").UsedRange

For i = 2 To UBound(r)

        r(i, 10).Value2 = RemoveWhiteSpace(r(i, 10))

Next i

End Sub

现在尝试这个我意识到我实际上是(I，9)但我在 RegExp 行上收到错误用户定义类型错误

Public Function RemoveWhiteSpace(target As String) As String

    With New RegExp
        .Pattern = "\s"
        .MultiLine = True
        .Global = True
        RemoveWhiteSpace = .Replace(target, vbNullString)
    End With
End Function

Sub stringRangeToClean()

Dim r As Variant
Dim i As Long
Dim txt As String


r = ActiveWorkbook.Sheets("Trent BASE DATA").UsedRange

For i = 2 To UBound(r)

       txt = r(i, 9)
       txt = RemoveWhiteSpace(txt)

Next i

End Sub

Answer 1

给你，不需要正则表达式来简单地删除空格。您的主要挑战只是定义您的范围，这是基本的 VBA:

Sub tgr()

    With ActiveWorkbook.Sheets("Trent BASE DATA")
        .Range("J2", .Cells(.Rows.Count, "J").End(xlUp)).Replace " ", vbNullString
    End With

End Sub