[RESOLVED] - Run macro when printing merged documents

[holm100]

Hello.

I want to run a macro when printing mail merged documents.
The document has a table with 22 rows. When merging with data, the mail merged documents have various numbers of empty rows depending on the data. I have found a macro which deletes the empty rows in the table. If I run the macro on the merged documents it deletes the empty rows on the first document which is fine. However, it deletes the same number of rows on the following documents which is a problem. I can give an example. The first document has 5 rows with data, so 17 rows gets deleted. The second documents has only 3 rows with data, so 19 rows should be deleted, but only 17 gets deleted because of the first document.
I would assume the solution was that the macro should run when printing all merged documents. Is there a way to do this or is there another solution.
The macro is here:

Sub DelAllBlankTableRows()
Application.ScreenUpdating = False
Dim Pwd As String, pState As Variant, bFit As Boolean, i As Long, j As Long
With ActiveDocument
  pState = False
  If .ProtectionType <> wdNoProtection Then
    Pwd = InputBox("Please enter the Password", "Password")
    pState = .ProtectionType
    .Unprotect Pwd
  End If
  For i = .Tables.Count To 1 Step -1
    With .Tables(i)
      bFit = .AllowAutoFit
      .AllowAutoFit = False
      For j = .Rows.Count To 1 Step -1
        On Error Resume Next 'skip vertically merged cells
        With .Rows(j)
          'ChrW(8194) is the formfield space character. Trim erases any other spaces
          If Trim(Replace(Replace(Replace(.Range.Text, Chr(13) & Chr(7), ""), _
            ChrW(8194), ""), Chr(160), "")) = "" Then .Delete
        End With
        On Error GoTo 0
      Next
      On Error Resume Next 'skip deleted tables
      .AllowAutoFit = bFit
      On Error GoTo 0
    End With
  Next
  If pState <> wdNoProtection Then .Protect Type:=pState, NoReset:=True, Password:=Pwd
End With
Application.ScreenUpdating = True
End Sub

Hope somebody can help!

[westconn1]

are you merging to the printer? or a document then printing?

pls use code tags when posting code

[holm100]

are you merging to the printer? or a document then printing?

pls use code tags when posting code

I have a document and use mail merge with data from an excel sheet. After using mail merge I can look through the merged documents. Then I run the macro. Then I print.

[incidentals]

the empty cells are in the data before merging or a result of merging?

ie can you fix the table before merging begins and have a cleaner merge if the problem is "pre merge"

here to help

[westconn1]

i tested your code on a very basic merged formletters document, it worked correctly without error

are you sure that the formletters document is the activedocument at the time?
i would be trying to avoid using the activedocument in the code, maybe

for each d in documents
   if instr(d.name, "FormLetters") > 0 then exit for
next
with d

assumes only one form letters document would be open

[holm100]

Thanks for the input.
I found a solution in Word. After the merge I should choose to merge them all to a new document. This collects all my letters into one new document. After that I run the macro which works fine. Then I print the document.

[westconn1]

After using mail merge I can look through the merged documents. Then I run the macro. Then I print.

I should choose to merge them all to a new document

i thought that was what you were doing already

[holm100]

i thought that was what you were doing already

Almost. The difference is that after the first merge, where you have the option to print from word, word also leaves an option of editing all documents as a new document. Waiting to run the macro in the new document solves the problem.