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]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you put this in a blank cell (say Z4) and drag to the right until you get #value, what are the numbers you get?

=UNICODE(MID($E4,COLUMNS($Z4:Z4),1))

 

[kbishop94]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

If you put this in a blank cell (say Z4) and drag to the right until you get #value, what are the numbers you get?

=UNICODE(MID($E4,COLUMNS($Z4:Z4),1))

Thank you for the suggestion to update my account info (its done.)

As far as the code, it did't like it.

 

[kbishop94]

(I copied the formula into the entire column that had values to make sure the issue wasn't with just that cell and it gave me the same #NAME? result)

 

[Fluff]

Ok Unicode didn't appear until 2013, which is why you get #Name
What does the message box return if you run this

Sub kbishop()
   Dim i As Long
   Dim msg As String
   
   For i = 1 To Len(Range("Y4"))
      msg = msg & AscW(Mid(Range("Y4").Value, i, 1)) & vbLf
   Next i
   MsgBox msg
End Sub

 

[kbishop94]

Thanks for your help on this, btw. (I thought it was going to be something really simple... but thats what I get for thinking!)

 

[Fluff]

Ok, you have a space & a carriage return at the end of the text, try

   With ActiveSheet.UsedRange
      .Replace vbCr, "", xlPart, , , , False, False
      .Replace " ", "", xlPart, , , , False, False
   End With

 

[kbishop94]

Ok, you have a space & a carriage return at the end of the text, try

   With ActiveSheet.UsedRange
      .Replace vbCr, "", xlPart, , , , False, False
      .Replace " ", "", xlPart, , , , False, False
   End With

Thank you, Fluff. That works... but I can only get it to clear the range of cells when I insert into a commandbutton. Ideally, I really would prefer it to clear out those characters when the module is running and after it pastes the data onto the worksheet;

Here is the code for when the data is copied-and-pasted onto the target worksheet "MTHLY" (including your new code):

' Clear ALL the cells of any previous data from the last calculation that was ran;
Worksheets("MTHLY").Activate
ActiveWorkbook.Worksheets("MTHLY").Range(Cells(3, 1), Cells(53, 38)).Select
Selection.ClearContents

' Copy new completed data calculations on the "FOURTHA" page;
Worksheets("FOURTHA").Activate
ActiveWorkbook.Worksheets("FOURTHA").Range(Cells(1, 77), Cells(25, 114)).Select
Selection.Copy

' Now paste data from the ("FOURTHA") worksheet onto the ("MTHLY") worksheet;
Worksheets("MTHLY").Activate
ActiveWorkbook.Worksheets("MTHLY").Range(Cells(3, 1), Cells(51, 38)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

' Clean the cells on the ("MTHLY")worksheet to remove carriage returns and spaces;
With ActiveSheet.UsedRange
     .Replace vbCr, "", xlPart, , , , False, False
     .Replace " ", "", xlPart, , , , False, False
End With

I also tried defining the range for the cells that I am trying to 'clean'... (which also doesn't work)

Dim tCol As Long
tCol = ActiveWorkbook.Worksheets("MTHLY").UsedRange.Rows.Count

With Worksheets("MTHLY").UsedRange
    ActiveWorkbook.Worksheets("MTHLY").Range(Cells(3, 1), Cells(tCol, 50)).Select
    .Replace vbCr, "", xlPart, , , , False, False
    .Replace " ", "", xlPart, , , , False, False
End With

However, this DOES WORK when its executed from a commandbutton that I placed onto the worksheet:

Private Sub cmdClean_Click()

Dim tCol As Long
tCol = ActiveWorkbook.Worksheets("MTHLY").UsedRange.Rows.Count

With Worksheets("MTHLY").UsedRange
    ActiveWorkbook.Worksheets("MTHLY").Range(Cells(3, 1), Cells(tCol, 50)).Select
    .Replace vbCr, "", xlPart, , , , False, False
    .Replace " ", "", xlPart, , , , False, False
End With
End Sub

 

[Fluff]

What if you try

With ActiveWorkbook.Sheets("MTHLY").UsedRange
     .Replace vbCr, "", xlPart, , , , False, False
     .Replace " ", "", xlPart, , , , False, False
End With

 

[kbishop94]

What if you try

With ActiveWorkbook.Sheets("MTHLY").UsedRange
     .Replace vbCr, "", xlPart, , , , False, False
     .Replace " ", "", xlPart, , , , False, False
End With

Nope. Unfortunately that did not work either.

Also, I didnt mention it, but both characters are still there (requires 2 keystrokes to remove them.)

(I just thought I should mention that in order to confirm that one of them wasnt removed and not the other... )

Thanks for your help on this...