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.

If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.

Leave a Reply


x

Interesting BlogPosts to read

How to print title on every page in Microsoft Excel?
Most of the time we might end up having a long workbook with huge amount of data and they have lot o...
How to change orientation of the worksheets in Microsoft Excel ?
This blog post will show the steps that are needed to change the orientation of the current workshee...
How to hide page breaks in worksheets in Microsoft Excel ?
When the page breaks is used to adjust the print area in a worksheet in excel , this would show up a...