How to remove blank lines from Word document generated using Excel
Category: [Excel] Demo Available
How to remove blank lines from Word document generated using Excel
Requirement:
The user is using VBA in Excel to generate a weekly report in Microsoft Word.
There are extra line breaks after the first line of the document and the beginning of the next line that the user would like to remove so there's only one blank line in between the first and second sentence of the document for example:
"Sat 28 Jun to Mon 6 Jul 2018
Second line begins here"
The user would also like to remove any blank lines at the end of the document.
Solution:
Code:
Sub m3()
Application.ScreenUpdating = False
On Error GoTo ErrTrap
Selection.HomeKey Unit:=wdStory
Selection.Find.ClearFormatting
With Selection.Find
.Text = "^p^p"
.Replacement.Text = "^p"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute
While Selection.Find.Found
Selection.MoveRight Unit:=wdCharacter, Count:=1
Selection.TypeBackspace
Selection.MoveLeft Unit:=wdCharacter, Count:=2
Selection.Find.Execute
Wend
ErrTrap:
Application.ScreenUpdating = True
End Sub
That will remove all occurrences of ^p^p. IF there are blank lines left at the end then those lines must contain some characters, even spaces.
Obtained from the OzGrid Help Forum.
Solution provided by XenoCode.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to copy non-blank rows in a range and paste to other sheets |
| How to find/return first nonblank value in adjacent cell from column with duplicate values |
| How to hide all rows with a blank or zero |
| How to use VBA to change zero value to blank value based on criteria in other columns |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.