VBA to substitute non-breaking space (CHAR(160)

Rpaikh

New Member
Joined
Jul 28, 2019
Messages
27
Hello,

I have code in excel to remove non-breaking space (CHAR(160).

Then I would like to apply this as the VBA script for the selection range and found some error.

Can anyone please point out what went wrong in my code?


VBA Code:
Sub Macro1()
'


Dim Cel As Range, rng As Range


Set rng = Selection.SpecialCells(xlCellTypeVisible)

With Application
        .ScreenUpdating = False
            For Each Cel In rng
                Cel.Value = Substitute(Trim(Cel), CHAR(160), """")
            Next
       
    End With



End Sub
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Substitute and trim are both worksheet functions. Try changing to worksheetfunction.substitute and worksheetfuntion.trim. I cannot test this since I am not at my computer.
 
Upvote 0
Hello I try this but still show an error

Capture.JPG


Substitute and trim are both worksheet functions. Try changing to worksheetfunction.substitute and worksheetfuntion.trim. I cannot test this since I am not at my computer.
 
Upvote 0
Maybe...

VBA Code:
Sub Macro1()
 Dim Cel As Range, rng As Range


    Set rng = Selection.SpecialCells(xlCellTypeVisible)

    Application.ScreenUpdating = False
    
    rng.Replace What:=Chr(160), Replacement:=Chr(32), LookAt:=xlPart
    
    For Each Cel In rng
        Cel.Value = Application.Trim(myCell.Value)
    Next
      
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
VBA Code:
Cel.Value = .Substitute(Trim(Cel), Chr(160), "")
 
Upvote 0
Hello MARK858,


Thank you! The result is okay, but some error (object required) pop-up on this line.

Capture2.JPG



Maybe...

VBA Code:
Sub Macro1()
Dim Cel As Range, rng As Range


    Set rng = Selection.SpecialCells(xlCellTypeVisible)

    Application.ScreenUpdating = False
   
    rng.Replace What:=Chr(160), Replacement:=Chr(32), LookAt:=xlPart
   
    For Each Cel In rng
        Cel.Value = Application.Trim(myCell.Value)
    Next
     
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
My bad...
VBA Code:
Sub Macro1()
 Dim Cel As Range, rng As Range


    Set rng = Selection.SpecialCells(xlCellTypeVisible)

    Application.ScreenUpdating = False
    
    rng.Replace What:=Chr(160), Replacement:=Chr(32), LookAt:=xlPart
    
    For Each Cel In rng
        Cel.Value = Application.Trim(Cel.Value)
    Next
      
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Thank you very much! This is also work perfectly.


My bad...
VBA Code:
Sub Macro1()
Dim Cel As Range, rng As Range


    Set rng = Selection.SpecialCells(xlCellTypeVisible)

    Application.ScreenUpdating = False
   
    rng.Replace What:=Chr(160), Replacement:=Chr(32), LookAt:=xlPart
   
    For Each Cel In rng
        Cel.Value = Application.Trim(Cel.Value)
    Next
     
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Hello footoo,


Wow this work perfectly !

Can you give some explanation for my knowledge what does it mean to put "." in front of the formula ?
Because your code was written like this :
VBA Code:
With Application
     Cel.Value = .Substitute(Trim(Cel), Chr(160), "")
End with
Which is the same as :
VBA Code:
 Cel.Value = Application.Substitute(Trim(Cel), Chr(160), "")
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top