vba - Putting Regex into VBA code

Question

I asked a question a week or so ago about removing all white-space from cells in a column and copy/pasting them into the column next to it. I got some help from someone to help do this but the code doesn't always work. Which is fine but the more I use the code the more issues seem to rise up. The VBA code is using R TRIM as its operation for trimming white-space but that doesn't seem to always work. There always seems to be a weird white-space character that wont delete. The code I put together before I asked my original question did this but deploying it to different workbooks was not working.

Here is my original

Function simpleCellRegex(myRange As Range) As String

    Dim Regex As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim strOutput As String

    strPattern = "\s+$"

        If strPattern <> "" Then
            strInput = myRange.Value
            strReplace = ""

        With Regex
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        If Regex.Test(strInput) Then
            simpleCellRegex = Regex.Replace(strInput, strReplace)
        Else
            simpleCellRegex = strInput
        End If
    End If
End Function

Here is the code that I got from asking for help

Option Explicit
Public Sub RemoveEndWhiteSpace()
    Dim arr(), i As Long, myRange As Range
    Set myRange = Selection
    If myRange.Columns.Count > 1 Or myRange Is Nothing Then Exit Sub
    If myRange.Count = 1 Then
        myRange = RTrim$(myRange.Value)
        Exit Sub
    Else
        arr = myRange.Value
        For i = LBound(arr, 1) To UBound(arr, 1)
            arr(i, 1) = RTrim$(arr(i, 1))
        Next i
        myRange.Offset(, 1) = arr
    End If
End Sub

I have tried multiple ways to put the Regex command into the working code but it always gives me an error. I tried to read up on VBA but I get nothing that really helps me. The reason I want it this way is because I work with huge amounts of data and the data can look very crazy. I want to clean up the crazy data by first removing the whitespace in each cell of the column, then outputting those cells into the column next to it. I would essentially like to combine the code but I have no idea how.

标签： vbaexcel

Answer 1

仅解决如何合并您的功能的问题...

未经测试：

Option Explicit
Public Sub RemoveEndWhiteSpace()
    Dim arr(), i As Long, myRange As Range
    Set myRange = Application.Intersect(Selection, ActiveSheet.UsedRange)
    If myRange Is Nothing Then Exit Sub
    If myRange.Columns.Count > 1 Then Exit Sub

    If myRange.Count = 1 Then
        myRange.Offset(0,1).Value = simpleCellRegex(myRange.Value)
    Else
        arr = myRange.Value
        For i = LBound(arr, 1) To UBound(arr, 1)
            arr(i, 1) = simpleCellRegex(arr(i, 1))
        Next i
        myRange.Offset(0, 1).Value = arr
    End If
End Sub


Function simpleCellRegex(v) As String

    Static Regex As RegExp

    'need to create/configure regex?
    If Regex Is Nothing Then
        Set Regex = New Regex
        With Regex
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = "\s+$"
        End With
    End If

    If Regex.Test(strInput) Then
        simpleCellRegex = Regex.Replace(v, "")
    Else
        simpleCellRegex = v
    End If

End Function