How to quickly remove multiple line breaks in Excel using VBA code?

There are times when you might copy few data from different source and you would end up getting unnecessary line breaks or carriage returns. If you quickly want to remove them , here’s a VBA code to do it.

How to quickly remove multiple line breaks in Excel using VBA code?

1. Open the Microsoft Visual Basic for Application window using the shortcut key Alt + F11 from Microsoft Excel.

2. Click Insert -> Module from the menu and paste the below code in the editor.

Sub RemoveLineBreaks()
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "Ginktage.com Sample"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    Rng.Value = Replace(Rng.Value, Chr(10), "")
Next
End Sub

3. Press F5 to run the program and you will be prompted to select the range of cell from which you want to remove the line breaks. Specify them and Click OK button and you would see the line breaks removed from the selected range of cells.

Leave a Reply

Your email address will not be published. Required fields are marked *