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]

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)

@Rick Rothstein Thank you so much for your continued effort. Unfortunately, this won't work pass the first review. When I double click on the review cell, it works the very first time. If the same person double clicks again to update their review it removes the first character and then enters their name on the second double click. Maybe I need to take a different approach. Is there an If statement I can use like <If Char(10) = "" Then delete it> or something with isblank?

 

[Rick Rothstein]

@Rick Rothstein Thank you so much for your continued effort. Unfortunately, this won't work pass the first review. When I double click on the review cell, it works the very first time. If the same person double clicks again to update their review it removes the first character and then enters their name on the second double click. Maybe I need to take a different approach. Is there an If statement I can use like <If Char(10) = "" Then delete it> or something with isblank?

Okay, try it this way...

If Left(Target.Offset(, 1), 1) = vbLf Then Target.Offset(, 1).Value = Mid(Target.Offset(, 1).Value, 2)

By the way, you told me there would always be a Line Feed to delete. Perhaps the following message that I have posted in the past will give you an insight into things from my side of the conversation...

One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data). To sum up... we only know what you tell us, nothing more.

 

[rdoulaghsingh]

Okay, try it this way...

If Left(Target.Offset(, 1), 1) = vbLf Then Target.Offset(, 1).Value = Mid(Target.Offset(, 1).Value, 2)

By the way, you told me there would always be a Line Feed to delete. Perhaps the following message that I have posted in the past will give you an insight into things from my side of the conversation...

One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data). To sum up... we only know what you tell us, nothing more.

@Rick Rothstein That worked perfectly! Thank you! My apologies for the incorrect info. I've been working on scripting different parts of this all weekend and my brain is fried. You are absolutely right and I will keep that in mind and try to be more diligent when asking for help. Again...thanks for all your willingness to help and swift reply on this.