Help with VBA code

[Legacy 227120]

I am new to VBA and am trying to get one to work. I have copied one from a forum and I'm pretty sure it will do what I require. The code is as follows:

Sub test()


Do
myRange.Replace What:=" ", Replacement:=" ", LookAt:=xlPart
Loop Until Application.CountIf(myRange, "* *") = 0



End Sub



However when I test it, an error comes up: "Run-time error 424: Object required". When I debug it highlights the following line (underlined above). I'm pretty sure its an easy fix, however being new to VBA its quite difficult for me!
Any help would be appreciated.

 

[Michael M]

What are you actually trying to do, as the code supplied is incomplete ??

 

[Legacy 227120]

I have an excel worksheet with a table of data, this was converted from a PDF file. However during the conversion it left multiple spaces between characters within each cell (4 spaces between each word to be exact).

I'm trying to get rid of all spaces between characters. I have tried the 'find & replace' and the 'trim' functions, but neither work.

I came across this VBA code on another forum, so wanted to try it out...

 

[Michael M]

Ok, here's acode to remove the excess spaces....but if it's an import from PDF, there may be other issues that are not really spaces

Sub MM1()
    Dim cell As Range
    For Each cell In ActiveSheet.UsedRange
        If Len(cell) > Len(WorksheetFunction.Trim(cell)) Then
            cell.Value = WorksheetFunction.Trim(cell)
        End If
    Next cell
End Sub

 

[Legacy 227120]

Thanks Michael.
You are spot on. I got a code from another forum and it didnt work the first time due to there being 'non-breaking spaces'. However I got it to work in the end.

Many thanks for your response!

 

[Michael M]

PLeasure, glad you got it sorted....