How to insert paragraphs between text and then export to word


New Member
Jul 23, 2013
Office Version
  1. 365

I have some code that combines text together. Because of the number of characters I have to export the text to word. I have two issues:

1) The formatting between the text is not correct. I want to apply spacing between my variables and then a page break between each new row.

2) I am really struggling to export to word and autofit the contents.

Here is my code:

VBA Code:
Sub inbrief()

Dim Cash As Range
Dim Title, activity, Description As String
Dim cell As Range
Dim CurrentRow, LastRow As Integer

Application.ScreenUpdating = False

CurrentRow = 3
'Clear sheet and filters
Sheets("In Briefs").Activate
Sheets("In Briefs").Range("$A$2:$H$1000").AutoFilter Field:=3
Sheets("In Briefs").Range("A3:H1000").ClearContents

'Copy SEDOLs from portfolio tab and bring in in briefs
Sheets("In Briefs").Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValues

'Remove all the blank rows
LastRow = ActiveSheet.UsedRange.Rows.Count
Range("A3:A" & LastRow).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

'Copy the vlookup formulas to import the paras
Range("B3:D" & LastRow).PasteSpecial xlPasteFormulas

'Join the header, activity and inbrief together and apply formatting
For Each cell In Range("F3:F" & LastRow)

If Range("C" & CurrentRow) <> "" Then
    With Application
        Title = Range("B" & CurrentRow).value
        Description = Range("C" & CurrentRow).value & Chr(13)
        activity = Range("D" & CurrentRow).value
    End With
        cell.value = Title & activity & Description
    With cell
        .Characters(1, Len(Title)).Font.Color = RGB(0, 89, 85)
        .Characters(1, Len(Title)).Font.Size = 18
        .Characters(Len(Title) + 1, Len(activity)).Font.Size = 11
        .Characters(Len(Title) + 1, Len(activity)).Font.Color = vbBlack
        .Characters(Len(Title) + Len(activity) + 1, Len(Description)).Font.Size = 11
        .Characters(Len(Title) + Len(activity) + 1, Len(Description)).Font.Color = vbBlack
     End With
End If

CurrentRow = CurrentRow + 1

'Filter out any securities that have no data
With Range("$A$3:$H$" & LastRow)
        .AutoFilter Field:=3, Criteria1:="="
    End With

Application.ScreenUpdating = True

End Sub

I've attached a screenshot of what it looks like.

Please can someone help me with the formatting and with exporting to word?



  • Capture.PNG
    64.4 KB · Views: 13

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)


Well-known Member
Jun 3, 2015
Office Version
  1. 365
  1. Windows
Not sure I can help but maybe this thread will provide some direction.
To add a new paragraph, use vbCrLf or Chr(13) & Chr(10). I could only see that you added Chr(13), which is not correct.
To add space between variables try concatenation: variableName1 & " " & variableName2
In a multi line declaration, you must explicitly type each variable as in
Dim Title As String, activity As String, Description As String

In your line, only Description is a string, the rest are variants because you didn't specify the data type. As long as the code can correctly interpret what you pass to the variable it will probably work. However, coding as if you expect a variable to hold a string when it can hold any data type is not a good idea.

Description might be a vba reserved word - it's a reserved word in Access but I doubt it will cause any issues for you. After this, I'm going to look for a list of reserved words for Excel.

As for your issue #2, I don't know enough about it to be of any help.

EDIT- apparently it is reserved. About half way down the page here is an Excel list
Last edited:
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back