Help with removing all 'new line' characters from range...

[kbishop94]

I think I am dealing with Char 10 (new line). I have tried:

Cell.Value = Trim(Cell)

.UsedRange.Replace Chr(10), ""

neither works... the spaces are still there.

when clicking into one of the cells in the formula bar, I see this:

"CSR0o "

When I click at the end of that string, the cursor is only ONE space over from the "o".

(and here is where its behaving strangely...)

When I hit the 'backspace' key to manually remove the space, the FIRST backspace keystroke doesn't do anything... (the cursor doesn't move.)
Its when I hit it a second time that it removes the 'space'.
And no, there is not two trailing spaces at the end of each string (using the 'arrow' keys you only have to move the left 1 space before you are into the string.)

Very weird.

Thanks for any help/assistance!

PS-

hopefully the picture will do a better job explaining what im attempting to....

 

[Fluff]

Unfortunately I have no idea why that is happening.

 

[kbishop94]

Unfortunately I have no idea why that is happening.

ok. Thanks for all your help tho. I will just use the command button method since that does work.

 

[kbishop94]

I got it to work. (well, still using a 'workaround', but now at least you dont have to use a separate commandbutton to remove those stubborn characters...)

Instead of a commandbutton, I just inserted the code into its own module, and then at the end of the series of modules that it cycles through, it runs this one last (the one with your code in it), and.... .... it works! It removes all the characters! Thanks again!

Private Sub cmdGetStats1_Click()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False

' First Section     =   21
' Second Section    =   22
' Third Section     =   23
' Fourth Section    =   19
' Fifth Section     =   20

Call First                              ' Module 21  (copy data from the mainworksheet and paste & performs calculation on the ("FIRSTA") workheet)

Call Second                         ' Module 22  (copy data from the mainworksheet and paste & performs calculation on the ("SECONDA") workheet)

Call Third                             ' Module 23  (copy data from the mainworksheet and paste & performs calculation on the ("THRIDA") workheet)

Call Fourth                           ' Module 19  (copy data from the mainworksheet and paste & perform calculations on the ("FOURTHA") workheet)

Call Clean                             ' Module 24 (remove carriage returns and spaces from the data on the ("MTHLY") worksheet)

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True

End Sub

and here is your code inserted into the new "CLEAN" module:

Sub Clean()
Worksheets("STATISTICS").Activate
With ActiveWorkbook.Sheets("MTHLY").UsedRange
     .Replace vbCr, "", xlPart, , , , False, False
     .Replace " ", "", xlPart, , , , False, False
End With
End Sub

 

[NdNoviceHlp]

Maybe....

Application.WorksheetFunction.Clean(Worksheets("STATISTICS").UsedRange)

HTH. Dave