Skip to main content

How to delete all blank lines or only the first line in cell in Excel?

Author: Siluvia Last Modified: 2022-07-21

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 Sub

3. 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 Sub

3. 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

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

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...

Description


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!
Comments (8)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
This Works it removed my all lines but the only issue is it is taking so much time to run for a single cell and i have big data with me to do it and i think it is gonna take tooo much time for that
This comment was minimized by the moderator on the site
Hi Tanveer Khan,
The code runs at normal speed on my side. Can you tell me which Excel version are you using?
This comment was minimized by the moderator on the site
This is my first VBA, I have only got up to your first step and it has solved my problem, so far. I have printed your instructions for future reference. So far I am wrapt. Fabulous instruction too, thank you for your quality skills and detailing them.
Rated 5 out of 5
This comment was minimized by the moderator on the site
Jeg forstår desværre ikke et kvæk af ovenstående. Findes der ikke en nem og ligetil måde at fjerne tomme linier i Exell? Helst uden koder og andet mystisk!

Med venlig hilsen
Lene
This comment was minimized by the moderator on the site
Hello,
If you don't want to keep the original texts in different lines in cells, you can apply the following methods to get it done.
1. Use the Find & Replace feature:
Press the Ctrl + H keys. In the opening Find and Replace dialog box, click to activate the Find what box. Hold down the Alt key while typing 010 on the numeric keypad. Keep the Replace with box empty, click the Replace All button. But all the texts is concentrated on the same line.
2. Use the following formula in a helper column:
=SUBSTITUTE(A1, CONCATENATE(CHAR(13),CHAR(10),CHAR(13),CHAR(10)),CONCATENATE(CHAR(13),CHAR(10)))
where A1 is the cell you want to process.
This comment was minimized by the moderator on the site
Do you know how to adapt this to remove the last line instead of the first line, please? TIA
This comment was minimized by the moderator on the site
Hi,
this works awesome. How can I adjust the code to remove only the lines, not starting with numeric characters?

Thanks and best,
Jack
This comment was minimized by the moderator on the site
Hi Jack,
Sorry can't help you with that yet. Thank you for your comment.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations