Microsoft Office Forums Mail Merge Template - remove blank rows in a table

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-09-2019, 05:53 AM
Litmus Litmus is offline Mail Merge Template - remove blank rows in a table Windows 10 Mail Merge Template - remove blank rows in a table Office 2013
Novice
Mail Merge Template - remove blank rows in a table
 
Join Date: Jan 2019
Location: Dorset, England
Posts: 3
Litmus is on a distinguished road
Default Mail Merge Template - remove blank rows in a table

Hello
I have a set of student grades in a spreadsheet.

Not all students study the same subjects. I'd like to run a mail merge reports for individual students that removes the subjects (hence rows in a table) that aren't studied and keep those that are i.e. those with grades.



See attachment.

Apologies if there is already a thread - I found related ones but not a specific one.
Attached Files
File Type: xlsx MWord Forum Image.xlsx (12.5 KB, 11 views)
Reply With Quote
  #2  
Old 01-09-2019, 01:46 PM
macropod's Avatar
macropod macropod is offline Mail Merge Template - remove blank rows in a table Windows 7 64bit Mail Merge Template - remove blank rows in a table Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,612
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Try adding the following macro to your mailmerge main document; when you click on Finish & Merge>Edit Individual Documents, the macro will automatically delete all table rows where the Grade is empty.
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 3 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
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #3  
Old 01-10-2019, 12:17 AM
Litmus Litmus is offline Mail Merge Template - remove blank rows in a table Windows 10 Mail Merge Template - remove blank rows in a table Office 2013
Novice
Mail Merge Template - remove blank rows in a table
 
Join Date: Jan 2019
Location: Dorset, England
Posts: 3
Litmus is on a distinguished road
Default

Thank you for taking the time to do this Paul. It's very much appreciated.
I shall give this a go. A quick question - will it work with more columns of data re:
For r = .Rows.Count To 3 Step -1
if I had say six columns maybe - please excuse my VBA ignorance.
Kind regards
Julie
Reply With Quote
  #4  
Old 01-10-2019, 12:48 AM
Litmus Litmus is offline Mail Merge Template - remove blank rows in a table Windows 10 Mail Merge Template - remove blank rows in a table Office 2013
Novice
Mail Merge Template - remove blank rows in a table
 
Join Date: Jan 2019
Location: Dorset, England
Posts: 3
Litmus is on a distinguished road
Default

Paul
This is the Mail Merge Template.The table that needs to change with the macro is Unit 1 Assessment. All of the others are static.
Attached Images
File Type: png Test Picture.PNG (62.4 KB, 29 views)
Reply With Quote
  #5  
Old 01-10-2019, 01:28 PM
macropod's Avatar
macropod macropod is offline Mail Merge Template - remove blank rows in a table Windows 7 64bit Mail Merge Template - remove blank rows in a table Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,612
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by Litmus View Post
A quick question - will it work with more columns of data re:
For r = .Rows.Count To 3 Step -1
if I had say six columns maybe - please excuse my VBA ignorance
The line:
For r = .Rows.Count To 3 Step -1
refers to rows, not columns. As coded, the macro tests column 2 (via the .Cells(2) reference). Provided that column is empty, the row will be deleted; the number of columns (provided there are at least 2) is of no consequence.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #6  
Old 08-23-2019, 02:53 PM
NoRest2Day NoRest2Day is offline Mail Merge Template - remove blank rows in a table Windows 10 Mail Merge Template - remove blank rows in a table Office 2013
Novice
 
Join Date: Aug 2019
Posts: 4
NoRest2Day is on a distinguished road
Default

Paul, this code is magic. Just curious, can you think of any way to do the same thing for an email merge. Realize the macro as written works on the merged file as an individual document.



Appreciate any help you can offer as I have been looking for a solution to this challenge for quite a while!
Reply With Quote
  #7  
Old 08-23-2019, 03:38 PM
macropod's Avatar
macropod macropod is offline Mail Merge Template - remove blank rows in a table Windows 7 64bit Mail Merge Template - remove blank rows in a table Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,612
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Quote:
Originally Posted by NoRest2Day View Post
Paul, this code is magic. Just curious, can you think of any way to do the same thing for an email merge.
No, because there's no post-merge 'document' to process.

Depending on what you're doing and how your data are configured, though, you might be able to
use a DATABASE field. See, for example: Many to one email merge using tables - Microsoft Community ; or
use IF tests with multiple versions of the table embedded within. See, for example: https://www.msofficeforums.com/128352-post6.html
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
  #8  
Old 08-24-2019, 05:35 PM
NoRest2Day NoRest2Day is offline Mail Merge Template - remove blank rows in a table Windows 10 Mail Merge Template - remove blank rows in a table Office 2013
Novice
 
Join Date: Aug 2019
Posts: 4
NoRest2Day is on a distinguished road
Default

Thanks for the quick reply and great ideas, Paul! Is it possible to parse the Individual Documents into an email merge by adding the email address --a two step process?
Reply With Quote
  #9  
Old 08-24-2019, 10:06 PM
macropod's Avatar
macropod macropod is offline Mail Merge Template - remove blank rows in a table Windows 7 64bit Mail Merge Template - remove blank rows in a table Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 19,612
macropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to beholdmacropod is a splendid one to behold
Default

Mailmerge automation could be used to generate individual documents and send them as email attachments or as the email body. If that's what you're after, I suggest you start a new thread. I'm no longer active on forums.
__________________
Cheers,
Paul Edstein
[MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Mail Merge Template - remove blank rows in a table How to remove almost a full page of blank space in between Word table rows garrisonsdad Word Tables 4 10-18-2018 09:09 PM
Mail Merge Template - remove blank rows in a table Mail merge: remove empty rows when a field is not showed Yarikh Mail Merge 4 09-23-2018 12:17 AM
Mail Merge Template - remove blank rows in a table Remove blank table rows AFTER mailmerge Formd Mail Merge 5 05-11-2018 03:43 PM
Mail Merge Template - remove blank rows in a table How to remove blank spaces between rows in a table, Jamal NUMAN Word 2 04-28-2017 12:59 PM
Mail Merge Template - remove blank rows in a table Mail merge into table with two colums and several rows kathriiin Mail Merge 3 03-30-2015 07:06 AM


All times are GMT -7. The time now is 06:02 PM.


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