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, 7 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,483
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, 11 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,483
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
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 07:29 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