Remove first blank line break in Excel using VBA

[rdoulaghsingh]

I'm using the below script to remove only blank line breaks using Chr(10). It works great, except that it doesn't remove the line break when it occurs at the beginning of the cell. I only need it to remove the first line break carriage from the top of the cell.

Sub RemoveBlankLines()
    Columns("W").Replace What:=Chr(10) & Chr(10), Replacement:=Chr(10), _
        LookAt:=xlPart, SearchFormat:=False, ReplaceFormat:=False
End Sub

Any help is appreciated! Thanks!

 

[rdoulaghsingh]

Ah, you are inside an event procedure (your first post showed you inside a macro), try replacing Selection with Target...

Target.Value = Replace(Target.Value, vbLf & vbLf, vbLf, , 1)

@Rick Rothstein Hmmm...Still leaves the first blank line break.

?​

 

[Rick Rothstein]

Are you saying there is a Line Feed character as the first character in the cell? If so, is that what you are trying to remove? Or is it interfering with removing the Line Feed between the first two sentence that you want to remove? Without having your data here in front of us, we are force to guess at the ultimate problem.

 

[Micron]

You're still describing, not posting before and after results so I don't think I'll be able to help.

when I dbl click in A in this row, it adds my username to the end of the list but does not add a blank before marconi or after peach or after my username.

marconi apple pear peach Micron

If it that happens to you something is different & you should post your complete code for this. Or use the Replace syntax first posted (or Rick's if you prefer).

 

[rdoulaghsingh]

Are you saying there is a Line Feed character as the first character in the cell? If so, is that what you are trying to remove? Or is it interfering with removing the Line Feed between the first two sentence that you want to remove? Without having your data here in front of us, we are force to guess at the ultimate problem.

@Rick Rothstein Yes - the code I'm using adds the first line as Char10. At the end of the execution after the username is added, I want to remove that first empty blank line feed only.

 

[Micron]

Yes - the code I'm using adds the first line as Char10.

Not for me - at least not that I can see. The only change I made was to the range:
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then

 

[rdoulaghsingh]

You're still describing, not posting before and after results so I don't think I'll be able to help.

when I dbl click in A in this row, it adds my username to the end of the list but does not add a blank before marconi or after peach or after my username.

marconi apple pear peach Micron

If it that happens to you something is different & you should post your complete code for this. Or use the Replace syntax first posted (or Rick's if you prefer).

Not for me - at least not that I can see. The only change I made was to the range:
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then

@Micron Code and before and after image attached.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

On Error Resume Next

If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("V1:V1309")) Is Nothing Then
  
   answer = MsgBox("Mark row as reviewed?", vbQuestion + vbYesNo + vbDefaultButton1, Environ("username"))
   If answer = vbYes Then

Application.ScreenUpdating = False

Application.DisplayAlerts = False

      With Target
         If InStr(1, .Offset(0, 1).Value, Environ("username")) = 0 Then
         .Offset(0, 1).Value = .Offset(0, 1).Value & Chr(10) & Environ("username")
         End If
         .Offset(0, 2) = " Last reviewed by: " & Environ("username") & " " & Now()
         Target.Offset(0, 1).Select
         Target.Value = Replace(Target.Value, vbLf & vbLf, vbLf, , 1)
         Selection.Value = Replace(Selection.Value, Chr(10), "", 1, 1)
         Selection.Value = Replace(Selection.Value, Chr(10) & Chr(10), Chr(10))
         'Msg = CreateObject("WScript.Shell").PopUp(ActiveSheet.Name & " updated successfully.", 1, "Info.")
         Cancel = True
      End With
    Else

Target.Offset(0, 1).Select

    End If
    
End If

'RemoveLineBreak

Application.ScreenUpdating = True

Application.DisplayAlerts = True
 
End Sub

 

[Micron]

I repeat - pictures are no good.

 

[rdoulaghsingh]

I repeat - pictures are no good.

@Micron I posted the code also. If you use the same code <If Not Intersect(Target, Range("A1:A10")) Is Nothing Then> and add an entry in B1 and then double click A1 , you'll see that it adds the second entry side by side.

 

[Micron]

I know you posted the code, and as I noted, I used it and changed the range to suit my sheet.
This is A1:C1 before dbl click in A:

apple pear orange

This is A1:C1 after dbl click in A1 as you instructed (using your last posted code):

..........

apple pear orange Micron

Last reviewed by: yulln 5/01/22 4:41:54 PM

Note: there are no line breaks in C1 - I checked; I added them here to show the Last reviewed is bottom aligned.
There are no line breaks before apple or after Micron - I checked.
There are no line breaks in A1 either.
Don't know what more I can do.

 

[EXCEL MAX]

Hope so I understand what you actually want.
I'm afraid it can't be done with one replacment.
Here is something that may works but I'm not sure how it looks on big size data.

Sub RemoveChr10()

    vA = Range("W1", Cells(Rows.Count, "W").End(xlUp))
    For vn = 1 To UBound(vA)
        vA(vn, 1) = Replace(Replace(Replace(Replace(vA(vn, 1), _
             Chr(10) & Chr(10), Chr(10) & Chr(10) & Chr(10)), _
             Chr(10) & Chr(10), Chr(10) & "Some rarely used character or text", 2), _
             "Some rarely used character or text" & Chr(10), ""), Chr(10) & _
             "Some rarely used character or text", "")
    Next vn
    Range("W1").Resize(UBound(vA), 1) = vA

End Sub