How to delete all blank lines or only the first line in cell in Excel?
You may receive a workbook with multiple lines mixing with blanks in cells. How to delete these blank lines in cells? And how about deleting only the first line? This article will help you to deal with it step by step.
Delete all blank lines in cells with VBA code
Delete only the first line in cells with VBA code
Delete all blank lines in cells with VBA code
As shown in the screenshot below, to delete all blank lines in the cells, you can run the following VBA code to get it done.
1. Press the Alt + F11 keys on your keyboard, then it opens a Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy and paste the below VBA code into the Module window.
VBA code: Delete all blank lines in cells
Sub DoubleReturn()
Dim xRng As Range, xCell As Range
Dim I As Integer
On Error Resume Next
Set xRng = Application.InputBox("Please select range:", "Kutools for Excel", Selection.Address, , , , , 8)
If xRng Is Nothing Then Exit Sub
On Error Resume Next
For Each xCell In xRng
For I = 1 To Len(xCell) - Len(WorksheetFunction.Substitute(xCell, Chr(10), ""))
xCell = Replace(xCell, Chr(10) + Chr(10), Chr(10))
Next
Next
End Sub3. Press the F5 key to run the code. Then select the cells with blank lines you will delete in the Kutools for Excel dialog box. And finally click the OK button.
Then you can see all blank lines are deleted from specified cells as below screenshot shown. The texts are still located in different lines.
Delete only the first line in cells with VBA code
As shown in the screenshot below, to delete the first line in cell A2 and A3, the following VBA code can help.
1. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, click Insert > Module. Then copy and paste the below VBA code into the Module window.
VBA code: Delete only the first line in cells
Option Explicit
Sub RemoveFirstLine(ByRef Target As Range)
Dim xCell As Range
For Each xCell In Target.Cells
xCell.Value = Right(xCell.Value, Len(xCell.Value) - InStr(1, xCell.Value, vbLf))
Next
End Sub
Sub StartRemove()
Dim xRng As Range
On Error Resume Next
Set xRng = Application.InputBox("Please select range:", "Kutools for Excel", Selection.Address, , , , , 8)
If xRng Is Nothing Then Exit Sub
On Error Resume Next
RemoveFirstLine xRng
End Sub3. Press the F5 key to run the code. In the popping up Kutools for Excel dialog box, please select the cells you need to delete only the first line, and then click the OK button.
Then you can see all first lines are deleted from specified cells as below screenshot shown.
Related article:
Best Office Productivity Tools
Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...
Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel (Full-Featured 30-Day Free Trial)
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day! (Full-Featured 30-Day Free Trial)
