VBA code - Break Points

[Fwiz]

Hello,

I have this vba code that select 2 different columns and splits out the text to next row in each cell - works fine (its not the cleanest of vba code) but for the range V9, where the data is split to next row i seem to end up with a space before the date

here is the code:
Sub Breakpoint_reset()

'******************************************************************************
'** Cleans up report and removes the "BR" element which is a key identifier **
'** to format the report **
'******************************************************************************

Dim rngCell As Range
Range("T9", Range("t10000").End(xlUp)).Select
With Selection
For Each rngCell In Selection
rngCell.Value = Replace(rngCell, "<br />", vbLf)
Next
End With
Range("V9", Range("V10000").End(xlUp)).Select
With Selection
For Each rngCell In Selection
rngCell.Value = Replace(rngCell, "<br />", vbLf)
Next
End With
Range("A9").Select

for Example, if the <br /> exists it will move to the next line in the cell.:
Expected output from the cell
21.01.2022 this is a message
01.12.201 refer to new note

but i seem to be getting this output (with a space)
21.01.2022 this is a message
(space here)01.12.201 refer to new note

I've checked the data - and no space exists, am i missing something?

thanks

FWIZ

 

[6StringJazzer]

It's hard to diagnose this without access to your data. If you only have this problem for V9 there is probably a character in your data that is unprintable but looks like a space. Somewhere on your sheet put this formula:

=CODE(MID(V9,FIND(CHAR(10),V9)+1,1))

And let us know what number it returns. 32 means it's a space.

 

[Fwiz]

yep, it shows 32 - is there a way i can remove the space from the start of each line feed within the cell?

 

[6StringJazzer]

A couple of comments on your code. It works but you are using With Selection then never taking advantage of that With. Also you do not need to Select a range to work on it. I suggest this update to your code (although it won't fix the problem you reported):

Sub Breakpoint_reset()

'******************************************************************************
'** Cleans up report and removes the "BR" element which is a key identifier **
'** to format the report **
'******************************************************************************

   Dim rngCell As Range
   For Each rngCell In Range("T9", Range("t10000").End(xlUp))
      rngCell.Value = Replace(rngCell, "<br />", vbLf)
   Next rngCell
   
   For Each rngCell In Range("V9", Range("V10000").End(xlUp))
      rngCell.Value = Replace(rngCell, "<br />", vbLf)
   Next
   
   Range("A9").Select

End Sub

 

[6StringJazzer]

If there is no space in your source data I am puzzled about how it's getting in there. However, the easiest way is

Sub Breakpoint_reset()

'******************************************************************************
'** Cleans up report and removes the "BR" element which is a key identifier **
'** to format the report **
'******************************************************************************

   Dim rngCell As Range
   For Each rngCell In Range("T9", Range("t10000").End(xlUp))
      rngCell.Value = Replace(Replace(rngCell, "<br />", vbLf), vbLf & " ", vbLf)
   Next rngCell
  
   For Each rngCell In Range("V9", Range("V10000").End(xlUp))
      rngCell.Value = Replace(Replace(rngCell, "<br />", vbLf), vbLf & " ", vbLf)
   Next
  
   Range("A9").Select

End Sub