Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-05-2019, 11:48 AM
cluyster1127 cluyster1127 is offline Blank Lines in Mail Merge - Other Options not working Windows 10 Blank Lines in Mail Merge - Other Options not working Office 2016
Novice
Blank Lines in Mail Merge - Other Options not working
 
Join Date: Dec 2019
Posts: 8
cluyster1127 is on a distinguished road
Default Blank Lines in Mail Merge - Other Options not working

Hello!

I am stumped, I have tried the options on here to suppress lines but it will not work. I have a file that is a "card" and each line has a piece of different information. But not all lines will have certain merges. I cannot get the blank lines to suppress. I"ve attached files to show what I have in my merge file, and what it looks like when it merges. Any help would be great, I cannot manually remove lines and the person who did it before me left NO directions.



Any help or guidance would be appreciated, I'm at my wits end!
Attached Images
File Type: png Merge File.PNG (40.4 KB, 23 views)
File Type: png Merged with Lines.PNG (36.9 KB, 23 views)
Reply With Quote
  #2  
Old 12-05-2019, 01:25 PM
macropod's Avatar
macropod macropod is offline Blank Lines in Mail Merge - Other Options not working Windows 7 64bit Blank Lines in Mail Merge - Other Options not working Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

You aren't suppressing/removing lines - those are table rows. The problem can be addressed by adding the following macro to your mailmerge main document (which you'll need to save in the .docm format):
Code:
Sub MailMergeToDoc()
Application.ScreenUpdating = False
Dim Tbl As Table, r As Long
ActiveDocument.MailMerge.Execute
With ActiveDocument
  For Each Tbl In .Tables
    With Tbl
      For r = .Rows.Count To 8 Step -1
        With .Rows(r)
          If Len(.Cells(2).Range.Text) = 2 Then .Delete
        End With
      Next
    End With
  Next
End With
Application.ScreenUpdating = False
End Sub
Now, when you click on Finish & Merge>Edit Individual Documents, the macro will automatically delete all table rows from the output where the second cell in the row is empty.

For PC macro installation & usage instructions, see: Installing Macros
For Mac macro installation & usage instructions, see: Word:mac - Install a Macro
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 12-06-2019, 08:37 AM
cluyster1127 cluyster1127 is offline Blank Lines in Mail Merge - Other Options not working Windows 10 Blank Lines in Mail Merge - Other Options not working Office 2016
Novice
Blank Lines in Mail Merge - Other Options not working
 
Join Date: Dec 2019
Posts: 8
cluyster1127 is on a distinguished road
Default

This was an amazing help, and last night I did it and it worked (I even cheered!) I came in this morning and went to do it again and it didn't work. Now i am confused

I did it through a VBA macro, and macro's are enabled, so I am confused what happened over night

I am getting a merge error that says "A frield calculation error occurred in record 317" which I wasn't getting last time, but my merge file hasn't changed.

Any ideas?
Reply With Quote
  #4  
Old 12-06-2019, 08:56 AM
cluyster1127 cluyster1127 is offline Blank Lines in Mail Merge - Other Options not working Windows 10 Blank Lines in Mail Merge - Other Options not working Office 2016
Novice
Blank Lines in Mail Merge - Other Options not working
 
Join Date: Dec 2019
Posts: 8
cluyster1127 is on a distinguished road
Default

Well now it worked, I removed the errored line, redid it all, it frozen, i had to shut it down, then when it recovered it worked. I'm very confused haha.
Reply With Quote
  #5  
Old 12-06-2019, 11:16 AM
cluyster1127 cluyster1127 is offline Blank Lines in Mail Merge - Other Options not working Windows 10 Blank Lines in Mail Merge - Other Options not working Office 2016
Novice
Blank Lines in Mail Merge - Other Options not working
 
Join Date: Dec 2019
Posts: 8
cluyster1127 is on a distinguished road
Default

and now another new error, same issue with the multiple blank rows, but a different file, and now i'm getting an error.
Attached Images
File Type: png Runtime Error 5941.PNG (10.5 KB, 25 views)
File Type: png debug.PNG (33.6 KB, 23 views)
Reply With Quote
  #6  
Old 12-06-2019, 02:25 PM
macropod's Avatar
macropod macropod is offline Blank Lines in Mail Merge - Other Options not working Windows 7 64bit Blank Lines in Mail Merge - Other Options not working Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

The macro is document-specific; it is only designed to work in the mailmerge main document described in post #1. That is why I said you should add the macro to that document; adding it to Word's Normal template, for example, would cause it to run on other mailmerges as well. Running the macro on another mailmerge main document will produce an error if the tables in it don't have at least two columns and, even then, may not produce the desired result.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 12-06-2019, 02:27 PM
cluyster1127 cluyster1127 is offline Blank Lines in Mail Merge - Other Options not working Windows 10 Blank Lines in Mail Merge - Other Options not working Office 2016
Novice
Blank Lines in Mail Merge - Other Options not working
 
Join Date: Dec 2019
Posts: 8
cluyster1127 is on a distinguished road
Default

Hello! Yes I only added it to the document, i thought since it was the same issue it would work on that document as well. could you help me do one for the new document?
Reply With Quote
  #8  
Old 12-06-2019, 03:03 PM
macropod's Avatar
macropod macropod is offline Blank Lines in Mail Merge - Other Options not working Windows 7 64bit Blank Lines in Mail Merge - Other Options not working Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

For any other mailmerge main document, you would need to change the '8' in:
For r = .Rows.Count To 8 Step -1
to specify the top row to test and the '(2)' in:
If Len(.Cells(2).Range.Text) = 2 Then .Delete
to specify the column to test.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #9  
Old 12-10-2019, 10:53 AM
cluyster1127 cluyster1127 is offline Blank Lines in Mail Merge - Other Options not working Windows 10 Blank Lines in Mail Merge - Other Options not working Office 2016
Novice
Blank Lines in Mail Merge - Other Options not working
 
Join Date: Dec 2019
Posts: 8
cluyster1127 is on a distinguished road
Default

Thank you so much, I did the change and found the numbers I needed for the debug to not come up and seem to work, but it still is showing the multiple empty rows on the new document.
I'm at a loss, and no one knows how the person before me did it, I am obviously not a microsoft expert!
Reply With Quote
  #10  
Old 12-10-2019, 01:25 PM
macropod's Avatar
macropod macropod is offline Blank Lines in Mail Merge - Other Options not working Windows 7 64bit Blank Lines in Mail Merge - Other Options not working Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Without seeing either the mailmerge main document or an output document, plus your modified code, it's impossible to know what the issue is.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #11  
Old 12-10-2019, 01:59 PM
cluyster1127 cluyster1127 is offline Blank Lines in Mail Merge - Other Options not working Windows 10 Blank Lines in Mail Merge - Other Options not working Office 2016
Novice
Blank Lines in Mail Merge - Other Options not working
 
Join Date: Dec 2019
Posts: 8
cluyster1127 is on a distinguished road
Default

I am so grateful for your help Paul! You really have saved me a lot of time so far.

See the attached. One is my merge doc, one is what my output is looking like, my data and the code. I made a tester rather than the full giant merge. The data file is typically saved as a csv due to the size, but it wouldn't let me upload csv.
Attached Images
File Type: jpg VBA Clergy cards.jpg (76.8 KB, 18 views)
Attached Files
File Type: docx 2019 Church Cards Merged Tester.docx (26.7 KB, 7 views)
File Type: docx Clergy Cards for Merger Tester.docx (26.7 KB, 8 views)
File Type: xlsx clergy data for cards - 2019 - Test.xlsx (15.1 KB, 6 views)
Reply With Quote
  #12  
Old 12-10-2019, 06:04 PM
macropod's Avatar
macropod macropod is offline Blank Lines in Mail Merge - Other Options not working Windows 7 64bit Blank Lines in Mail Merge - Other Options not working Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

You changed more than you were advised. Read post #8 more closely.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #13  
Old 12-11-2019, 07:07 AM
cluyster1127 cluyster1127 is offline Blank Lines in Mail Merge - Other Options not working Windows 10 Blank Lines in Mail Merge - Other Options not working Office 2016
Novice
Blank Lines in Mail Merge - Other Options not working
 
Join Date: Dec 2019
Posts: 8
cluyster1127 is on a distinguished road
Default

I see it now! I just changed the 1 back to a 2 and left the (1) the same and it worked. Thank you so much for guiding me and now i Understand how that piece works on an file like this for future! I will also be saving this for anyone who needs it if I am ever not here.
Thank you so much for all your help!
Reply With Quote
  #14  
Old 12-11-2019, 01:05 PM
macropod's Avatar
macropod macropod is offline Blank Lines in Mail Merge - Other Options not working Windows 7 64bit Blank Lines in Mail Merge - Other Options not working Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

You're welcome.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Blank Lines in Mail Merge - Other Options not working Mail merge returning blank field SHA Mail Merge 3 05-03-2019 01:54 PM
Blank Lines in Mail Merge - Other Options not working I am getting blank lines with Mail Merge rtcary Mail Merge 1 01-20-2018 02:07 PM
Blank Lines in Mail Merge - Other Options not working Mail Merge Macro - With Options? MacroNoob Mail Merge 1 09-08-2017 12:11 AM
Blank Lines in Mail Merge - Other Options not working Eliminating blank lines in mail merge (labels) BeerStud Mail Merge 1 11-28-2014 06:29 PM
Message Options - Mail Merge limitations nai0248 Mail Merge 0 12-09-2010 01:39 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 08:55 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft