Remove white spaces in a single column

Question

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

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

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

You're just removing spaces? This can be done with a single line of code: `ActiveWorkbook.Sheets("Trent BASE DATA").Cells.Replace " ", vbNullString` — tigeravatar, Dec 22 '17 at 14:13
no I only want to remove spaces in column (I, 10) or column I but only if there is a value in there hence why I have used the .usedRange and used a loop to go down the sheet and look at the offset (0,10) of the range — Leighholling, Dec 22 '17 at 14:14
Then instead of `.Cells` change that portion to `.Range("J:J")`. And if there's not a value in the cell, it won't change anyway so no need to try to exclude those, it won't add any time. — tigeravatar, Dec 22 '17 at 14:16
I know the .value2 is wrong but it doesn't work with the .value2 removed — Leighholling, Dec 22 '17 at 14:17

score 6 · Accepted Answer · answered Dec 22 '17 at 14:20

6

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

Sub tgr()

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

End Sub

answered Dec 22 '17 at 14:20

tigeravatar

26,199
5
30
38

I think in this way if I have a blank cell half way up it will stop – Leighholling Dec 22 '17 at 14:25
1

Did you actually try it? You should try it. – tigeravatar Dec 22 '17 at 14:26
yes it works now ive selected the right column thanks – Leighholling Dec 22 '17 at 14:31
I like this code just out of interest is there a way to reverse this once I have finished to make the original data readable – Leighholling Dec 22 '17 at 14:44
You'd need to store the original data in an array, or copy it to another column before removing the white spaces. – tigeravatar Dec 22 '17 at 14:46
I think this what I need to do would use the array approach. unless I can compare the data to another cell which is what im doing but make it not compare the number or position of spaces – Leighholling Dec 22 '17 at 14:48
It sounds like you have another goal here. If your actual goal is simply to compare cells while ignoring spaces, use the [SUBSTITUTE function](https://support.office.com/en-us/article/SUBSTITUTE-function-6434944e-a904-4336-a9b0-1e58df3bc332) like so: `=SUBSTITUTE(A1," ","")=SUBSTITUTE(B1," ","")` – tigeravatar Dec 22 '17 at 14:50
And if you do actually have a different goal than what you've asked, that is an [XY Problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). Always start with your intended goal, or you'll get wayward answers. – tigeravatar Dec 22 '17 at 14:52
Has not changed I'm just looking at a different approach do I remove or substitute that's all thanks – Leighholling Dec 22 '17 at 14:55

Vityata · Answer 2 · 2017-12-22T14:28:46.937

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.

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.

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

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.

then `R` should be something like this = `Intersect(Worksheets("Trent BASE DATA").UsedRange,Worksheets("Trent BASE DATA").columns("J"))`. But then you should check whether it is Not Nothing, in case that Column `J` is empty. — Vityata, Dec 22 '17 at 14:35
heres another related question instead of removing can I compare each cell to another cell but ignore spaces, so that only characters are been compared not the number or position of spaces — Leighholling, Dec 22 '17 at 14:42

Remove white spaces in a single column

2 Answers2