Delete whitespaces vba Excel

Question

I want to delete all whitespaces in a range and got the following code

Dim rng2 As Range
Dim cleanString2 As String
Set rng2 = ContactData.Range("AR2:AR" & lRow)
rng2.Value = Application.Trim(rng2)
For j = 2 To lRow
    cleanString2 = ContactData.Range("AR" & j).Value
    cleanString2 = Replace(cleanString2, Chr(10), "")
    ContactData.Range("AR" & j).Value = cleanString2
Next j

I got and equivalent code with same variables and everything just without the 2 in the variable names the code works, but the other code goes for colum A instead of AR, can anybody help me with finding the bug? Thanks!

Dim rng As Range
Dim cleanString As String
Set rng = ContactData.Range("A2:A" & lRow)
rng.Value = Application.Trim(rng)
For i = 2 To lRow
    cleanString = ContactData.Range("A" & i).Value
    cleanString = Replace(cleanString, Chr(10), "")
    ContactData.Range("A" & i).Value = cleanString
Next i

this is the working code, below a picture of the variable values while debugging:

Answer 1

Rather than do it row by row you could replace all whitespace in one hit.

The problem you're finding is that you're referencing different ranges.
The original code states:
Set rng = ContactData.Range("A2:AR" & lRow)

Your code has:
Set rng = ContactData.Range("A2:A" & lRow) - missing the ref to column AR.

You could use the below code to remove the whitespace (Chr(32). As @Rory said - Chr(10) is a linefeed.

Sub Test()

    Dim rng As Range
    Dim lRow As Long

    lRow = 15
    Set rng = ContactData.Range("A2:AR" & lRow)

    rng.Replace What:=Chr(32), Replacement:="", LookAt:=xlPart

End Sub

Answer 2

Judging from your code, the problem is that you are hoping that:

cleanString = Replace(cleanString, Chr(10), "")

would remove the whitespaces. Chr(10) is not a whitespace as per the ASCII table. Thus, the easiest way is probably to go like this:

cleanString = Replace(cleanString, " ", "")

If this does not work, as a workaround try this:

Public Sub RemoveSpaceInString()    
    Dim myCell As Range            
    For Each myCell In Selection
        myCell = Trim(myCell)
        myCell = Replace(myCell, vbTab, "")
        myCell = Replace(myCell, " ", "")
        myCell = Replace(myCell, Chr(160), "")
    Next myCell    
End Sub

It uses Selection, because it was intended to be used outside working code, as a "format-helping" tool. However, if it works, it would be quite easy to write it inside your code.