CODEWITHSUNDEEP
vbaexcel
Sarion
Sarion

Reputation: 5

Putting Regex into VBA code

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.

Upvotes: 0

Views: 154

Answers (2)

PaulG
PaulG

Reputation: 1189

'Replaces all data characters in the data range that are not defined in the pattern with the Replace character.

Public Sub RegExMatchAndReplace(ByVal Pattern As String, ByVal Data As Range, ByVal Replace As String)

'//////////////////////////////////////////////////////////////////////////

Dim oRegEx As New RegExp
Dim rPtr As Range

'///////////////////////////////////////////////////////////////////////////
For Each rPtr In Data
    With oRegEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
        .Pattern = Pattern
        rPtr.Value = oRegEx.Replace(rPtr.Value, Replace)
    End With
Next

End Sub

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166126

Addressing only the question of how to incorporate your function...

Untested:

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

Upvotes: 1

Related Questions