Remove Characters/Spaces after text for all cells within Range

[grady121]

Initially I’m simply copying a data table from a web page using “Ctrl + A” then “Ctrl + C”, and then pasting the data straight onto a new worksheet so I can work with it. (After temporarily re-naming the old sheet)

But I keep finding what looks like double-spaces after some of the important text within the Range of cells I’m working with. I need to be able to select & conditional format the values of the text in some columns of the sheet, so need to loose these trailing spaces.

Unfortunately, it’s not consistence as to how many spaces trail the text I need. Sometimes it’s only one space, sometimes its two spaces ?

So far, I’ve had mixed success with a recorded "Replace" code but none of the other codes I have found on forum pages either don’t work all or seem to give any consistent results. E;g; TRIM, CLEAN

I suspect my problem is, I do not know how to call the code properly, or trying to work with too large a range ?

Can anyone help?

The start of my code reads:

    Sheets("Data").Select
    Sheets("Data").Name = "Old Data"
    
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets(Sheets.Count).Select
    ActiveSheet.Name = "Data"
    ActiveSheet.Paste ' Copied info from web page
    Selection.Copy
    ' Try and remove some of the unwanted characters ?
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False        
    ActiveSheet.Hyperlinks.Delete

    
    ' At the moment I’m using to select the pasted range I want to work on:
    Range(Range("C46"), Range("C46").SpecialCells(xlLastCell)).Select 


‘ This is where I need a code to work on the new Data sheet and remove all the trailing characters.


    MsgBox "All data cleaned successfully !", vbInformation + vbOKOnly, "All Done"

Any help to a beginner welcome.

 

[wigi]

Try ASAP utilities: it has built-in functionality to remove trailing spaces.
As well as clean copies from webpages.
And a lot more.

 

[VoG]

Or this Rearranging Data in Columns

Select the range then run the TrimAll macro.

 

[grady121]

Thanks for the prompt replies.

The TrimAll macro suggested by VoG was just what I needed.

Thanks again.