How to delete all blank lines or only the first line in cell in Excel?
You may receive a workbook with cells containing multiple lines of text mixed with blank lines. How to delete these blank lines in cells? And how about deleting only the first line? This article will guide you through the process 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.
You will see that all blank lines are removed from the specified cells, as shown in the screenshot below. 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.
You will see that the first lines are removed from the specified cells, as shown in the screenshot below.
Related article:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
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!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite ā Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license ā set up in minutes (MSI-ready)
- Works better together ā streamlined productivity across Office apps
- 30-day full-featured trial ā no registration, no credit card
- Best value ā save vs buying individual add-in