How to remove extra lines in cell's content

0

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.

Answer
Discuss

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.

Discuss

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.
Variatus (rep: 823) Sep 14, '17 at 8:54 pm
Add to Discussion
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!

Discuss
0

How do you save the module to a macro?

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login