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!

 

[Micron]

Consider vba Replace function instead of Excel Replace function because the former has a count parameter for the number of times to make a replacement. So remove the first Chr(10) one time, then remove the concatenated ones. Using Selection property for easier testing:

Selection.Value = Replace(Selection.Value, Chr(10), "", 1, 1)
Selection.Value = Replace(Selection.Value, Chr(10) & Chr(10), Chr(10))

 

[rdoulaghsingh]

Consider vba Replace function instead of Excel Replace function because the former has a count parameter for the number of times to make a replacement. So remove the first Chr(10) one time, then remove the concatenated ones. Using Selection property for easier testing:

Selection.Value = Replace(Selection.Value, Chr(10), "", 1, 1)
Selection.Value = Replace(Selection.Value, Chr(10) & Chr(10), Chr(10))

Hello @Micron! Thanks again for helping out. I tried a few variations to the sample above, but it removes the line breaks without blanks and concatenates the text in the cell.

 

[Micron]

I does what I thought you wanted - so I guess I don't know exactly what that is. Can you post some before and after examples? Not sure what blanks are here - blank lines? Blank spaces?

 

[rdoulaghsingh]

I does what I thought you wanted - so I guess I don't know exactly what that is. Can you post some before and after examples? Not sure what blanks are here - blank lines? Blank spaces?

@Micron The previous script you helped with on the other thread produces a blank line on the first entry. I'm trying to eliminate that one blank line from the very first entry. Image attached.

 

[Micron]

Sorry, I don't know what other thread you're referring to. Should you post a link to it?
Still don't know what a blank is to you. While the pic may be accurate, you have to consider that I cannot see what you're looking at. Is that a pic of 4 lines in one cell and the first one is blank? If I have this in one cell and run the code I posted here

dog cat bird

I get

dog cat bird

Note that the first blank row is removed and so are the ones in between the words (because there's 2 line feeds in between dog and cat, for example).

 

[Rick Rothstein]

Does this do what you want...

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

 

[rdoulaghsingh]

Sorry, I don't know what other thread you're referring to. Should you post a link to it?
Still don't know what a blank is to you. While the pic may be accurate, you have to consider that I cannot see what you're looking at. Is that a pic of 4 lines in one cell and the first one is blank? If I have this in one cell and run the code I posted here

dog cat bird

I get

dog cat bird

Note that the first blank row is removed and so are the ones in between the words (because there's 2 line feeds in between dog and cat, for example).

@Micron Append text to a cell when a label is clicked. Yes - 4 lines in 1 cell with the first line blank. When I run the code it concatenates the first 2 line items in one line.

 

[rdoulaghsingh]

Does this do what you want...

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

@Rick Rothstein Thanks for chiming in. The code didn't yield any results for me for some reason. Script block is 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)

         '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]

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)