VBA to Remove ASCII Character 32

[legalhustler]

I have two sheets ("Old" and "New") with various range of data and would like them to be formatted the same so that it removes ASCII character 32 and replaces them with a null text string "".

Can someone help with creating this macro.


Thanks.

 

[Takae]

Hi,
ASCII character 32 is space?
Ctrl + H ?

 

[legalhustler]

Hi,
ASCII character 32 is space?
Ctrl + H ?

Yes - ASCII character 32 is space, but I am looking specifically for a code to do this as it is just a small subset of a larger code that compares the two sheets. Thanks.

 

[StephenCrump]

Reposted from here: http://www.mrexcel.com/forum/excel-questions/869566-similar-looking-texts-not-same.html

I thought your problem was converting CHAR(160) to normal spaces? In which case:

Cells.Replace Chr(160), " ", xlPart

You can use similar code to remove all regular space characters if you like, perhaps:

Range("SomeRangeName").Replace " ", "", xlPart

 

[legalhustler]

Cells.Replace Chr(160), " ", xlPart

I tried the above code but it's not removing the extra hard coded spaces. I needed to replace code 32 from all cells with no spaces ("") or with code 160. Code 32 means it has space and code 160 means non-breaking space.

On one sheet a text like "Hello World" has code 32 and on another sheet the same text has code 160. All the characters need to be in the same format. Therefore, I want to replace code 32 from the one sheet to code 160.

Please advise. Thanks.

 

[legalhustler]

This is my entire code (note the bold part):

Sub UnmergePasteValuesDeleteBlankRows()
Dim LR As Long, Rw As Long, delRNG As Range, ws As Worksheet

For Each ws In ActiveWorkbook.Sheets
    Select Case ws.Name
        Case Is = "Old", "New"

    LR = ws.UsedRange.Rows.Count
    
    With ws.UsedRange
        .UnMerge
        .Value = .Value
        [B]Selection.Replace Chr(32), "", xlPart[/B]

    End With
    
    On Error Resume Next
        
    For Rw = 1 To LR
        If WorksheetFunction.CountA(ws.Rows(Rw).EntireRow) = 0 Then
            If delRNG Is Nothing Then Set delRNG = ws.Rows(Rw) Else Set delRNG = Union(delRNG, ws.Rows(Rw))
        End If
    Next Rw
        
    If Not delRNG Is Nothing Then delRNG.EntireRow.Delete xlShiftUp
    Set delRNG = Nothing
    End Select
Next ws

End Sub

 

[legalhustler]

Issue resolved.

I used the following VBA code: Cells.Replace Chr(32), Chr(160), xlPart

Now both sheets has the same ascii codes

Thanks!

 

[StephenCrump]

No problem. I'm glad you figured it out.