Hello,
I want to remove the extra lines in cell contents.
Example:
Line 1
.
.
.
Line 2
.
.
.
Line 3
.
.
.
Line 4
I need output like below:
Line 1
Line 2
Line 3
Line 4
Only single blank line should remain between two lines.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
How to remove extra lines in cell's content
0
Answers
0
Please try this code. Paste it to a standard code module of your workbook and remember to save it as macro enabled thereafter. The code will run on the ActiveSheet and delete extra rows where cells in column A are blank. It willl also insert rows where no blank is found.
Sub DoubleSpacing()
' 14 Sep 2017
Dim Blanks As Integer
Dim R As Long
Application.ScreenUpdating = False
With ActiveSheet
' look for values in column A
R = .Cells(.Rows.Count, "A").End(xlUp).Row
' loop from the end to row 2
For R = (R + 1) To 2 Step -1
If Len(.Cells(R, "A").Formula) Then
If Blanks Then
Blanks = 0
Else
.Rows(R + 1).EntireRow.Insert
End If
Else
If Blanks Then
.Rows(R).EntireRow.Delete
Else
Blanks = 1
End If
End If
Next R
End With
Application.ScreenUpdating = True
End Sub
Note that the code doesn't check if there might be cell content in columns other than column A. It wouldn't be very difficult to amend it to ensure that no non-blank rows are deleted but doing so would slow down the process quite a bit.
Discussion
Please don't post your questions as answers.
While on the worksheet, press Ctl+F11 to open the VB Editor window. In the Project Explorer window on the left look for the VBA project of the workbook in which you want the code. Right-click on that name. From the dropdown that opens select 'Insert' and 'Module'. Check the file name at the top of the screen. It should now end on "- [Module 1 (Code)]", meaning you are looking at the code sheet you just inserted. Paste the code into the blank space on the right side of your screen.
Place the cursor anywhere in the code and press F5 to run the code. Make sure that you have backup of your data because the code will make changes to the worksheet which you last looked at before pressing Ctl+F11.
While on the worksheet, press Ctl+F11 to open the VB Editor window. In the Project Explorer window on the left look for the VBA project of the workbook in which you want the code. Right-click on that name. From the dropdown that opens select 'Insert' and 'Module'. Check the file name at the top of the screen. It should now end on "- [Module 1 (Code)]", meaning you are looking at the code sheet you just inserted. Paste the code into the blank space on the right side of your screen.
Place the cursor anywhere in the code and press F5 to run the code. Make sure that you have backup of your data because the code will make changes to the worksheet which you last looked at before pressing Ctl+F11.
Variatus (rep: 4889) Sep 14, '17 at 8:54 pm
0
You could set up a Filter on the data. Then remove Blank lines from the Data Range. Not sure what version of office you're using. It's under Data Filter. No VBA required. Simple!
0
How do you save the module to a macro?