Cloud Training (Online)

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 = " 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), "")
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.

Senthil Kumar B
Senthil Kumar is a former Microsoft MVP (Most Valuable Professional). He is a Co-Author of the book "Windows 10 Development Recipes using JavaScript and CSS" for Apress Publication. He is a technical presenter, blogger, mentor and a Geek.  Senthil is a regular speaker is various local user groups. He has presented at conferences like Great Indian Developer Summit (GIDS) & Microsoft DevCamps. You can reach out to him via his Twitter handle @isenthil.