删除单列中的空格

I have a sheet that I need to remove the spaces from so I can compare it I am using a function I found on here and a sub that puts all the values into an array (for speed) but I cannot get it to work any idea why. I get a

我有一张表,我需要删除空格,所以我可以比较它我使用的是我在这里找到的函数和一个将所有值放入数组的子(为了速度)但是我无法让它工作任何想法为什么。我得到了

ByRef argument type mismatch error

ByRef参数类型不匹配错误

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

now trying this have I realised the col I is actually (I,9) but im getting an error user defined type error on the RegExp line

现在尝试这个我已经意识到col我实际上(我,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

2 个解决方案

#1

Here you go, no regex required for simply removing spaces. Your main challenge is just defining your range which is basic VBA:

在这里,只需删除空格就不需要正则表达式。您的主要挑战是定义您的基本VBA范围:

Sub tgr()

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

End Sub

#2

Try like this:

试试这样:

Public Function RemoveWhiteSpace(target As String) As String

    Dim RegExp    As Object
    Set RegExp = CreateObject("VBScript.RegExp")

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

This way you are using a late binding for the regExp variable, thus neither you nor your users have to add any additional library.

这样您就可以使用regExp变量的后期绑定,因此您和您的用户都不必添加任何其他库。

If you want to use the early binding, you should add the Microsoft VBScript Regular Expressions 5.5" library to the ones, that VBA uses. The early binding gives you some time bonus and it provides IntelliSense.

如果要使用早期绑定,则应将Microsoft VBScript正则表达式5.5“库添加到VBA使用的库中。早期绑定会为您提供一些时间奖励并提供IntelliSense。

Here the selected answer explains step-by-step how to add the library:

这里所选答案逐步说明了如何添加库:

How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops

如何在Microsoft Excel中使用正则表达式(正则表达式)在单元格和循环中

Concerning your code - this is very simply loop, try to do it:

关于你的代码 - 这是非常简单的循环,尝试这样做:

Sub StringRangeToClean()

    Dim r           As Variant
    Dim myCell      As Range

    r = ActiveWorkbook.Sheets("Trent BASE DATA").UsedRange        
    For Each myCell In r
        myCell = RemoveWhiteSpace(txt)
    Next myCell

End Sub

This way every cell would be examined and the white spaces would be removed.

这样,每个单元都将被检查,并且白色空间将被移除。

#1