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!

 

[Rick Rothstein]

@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.

So now I am not sure where in your code you want or need this to happen so, for example purposes, I'll use Cell as the variable name (it may actually be Target if inside an event procedure or Selection if you are trying to do this to a selected cell). To remove a leading Line Feed...

If you know for sure there is always a leading LineFeed, then...
V = Mid(V, 2)

If there might be a Line Feed but also might not be one, then...
If Left(V, 1) = vbLf Then V = Mid(V, 2)

 

[rdoulaghsingh]

So now I am not sure where in your code you want or need this to happen so, for example purposes, I'll use Cell as the variable name (it may actually be Target if inside an event procedure or Selection if you are trying to do this to a selected cell). To remove a leading Line Feed...

If you know for sure there is always a leading LineFeed, then...
V = Mid(V, 2)

If there might be a Line Feed but also might not be one, then...
If Left(V, 1) = vbLf Then V = Mid(V, 2)

@Rick Rothstein At the end of the procedure it activates the cell I would like to remove the leading blank linefeed from. And yes. it always produces a leading blank linefeed. The code would be inserted where it says <Code Here> below:

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
         'Selection.Value = Replace(Selection.Value, vbLf & vbLf, vbLf, , 1)
         <<<CODE HERE>>>
         '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

 

[Rick Rothstein]

Try this...

Target.Value = Mid(Target.Value, 2)

 

[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 Thanks again! I gave it a try, but it's removing a character from the cell to the left of the target (image attached).

 

[rdoulaghsingh]

.

 

[rdoulaghsingh]

@EXCEL MAX Thanks for the code. This one might be a little bit out of my wheelhouse. I'm new to VBA. I just need a one-liner to remove a blank linefeed in the first line of the active cell.

 

[EXCEL MAX]

It's OK. We often meets cases like this when is hard to explain problem,
and also is hard to us to determine level of people's knowledge.
Hope so you are successfully resolve this small problem.

 

[Rick Rothstein]

@Rick Rothstein Thanks again! I gave it a try, but it's removing a character from the cell to the left of the target (image attached).

which cell has the Line Feed at the beginning, the one on the right or on the left?

 

[rdoulaghsingh]

which cell has the Line Feed at the beginning, the one on the right or on the left?

@Rick Rothstein The one on the right. The position I'm adding The code though has the macro activating that cell just before the line you gave me.

 

[Rick Rothstein]

So you are double-clicking a cell and you want the cell in the next column to have its leading Line Feed removed? If so...

Target.Offset(, 1).Value = Mid(Target.Offset(, 1).Value, 2)