Microsoft Office Forums

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, 27 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: 20,558
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
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
[Fmr 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, 54 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: 20,558
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
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
[Fmr 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: 20,558
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
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
[Fmr 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: 20,558
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
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.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #10  
Old 11-21-2020, 02:21 AM
Novice2020 Novice2020 is offline Mail Merge Template - remove blank rows in a table Mac OS X Mail Merge Template - remove blank rows in a table Office 2011 for Mac
Novice
 
Join Date: Nov 2020
Posts: 1
Novice2020 is on a distinguished road
Default

Thanks so much for this. The macro works if there is only the table in question. I have more table above this where I won't need to apply this macro to tables above - is there a way I can only apply the macro to a specific table on word before mail merging?
Reply With Quote
  #11  
Old 11-21-2020, 03:03 AM
macropod's Avatar
macropod macropod is offline Mail Merge Template - remove blank rows in a table Windows 10 Mail Merge Template - remove blank rows in a table Office 2010
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,558
macropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant futuremacropod has a brilliant future
Default

No, that's not possible, because the tables concerned don't exist in a populated form until after the merge has completed. Instead, the code could be re-written to process every nth table. For example:
Code:
Sub MailMergeToDoc()
Application.ScreenUpdating = False
Dim t As Long, r As Long
ActiveDocument.MailMerge.Execute
With ActiveDocument
  For t = 1 To .Tables.Count Step 2
    With .Tables(t)
      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
where the '1' indicates the table to start at, and the '2' indicates that every second table from the table to start at is to be processed. Thus, as coded, the 1st, 3rd, 5th, etc. tables would be processed.
__________________
Cheers,
Paul Edstein
[Fmr 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

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 11:16 PM.


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