Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Closed Thread
 
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, 84 views)
  #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: 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

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]
  #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
  #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, 147 views)
  #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: 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

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]
  #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!
  #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: 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

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]
  #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?
  #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: 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

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]
  #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?
  #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: 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

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]
  #12  
Old 12-02-2020, 04:53 AM
Richystab Richystab is offline Mail Merge Template - remove blank rows in a table Windows 10 Mail Merge Template - remove blank rows in a table Office 2019
Novice
 
Join Date: Dec 2020
Posts: 12
Richystab is on a distinguished road
Default Struggling to make this work using word2019

Hi Please could someone help me re create this. I cannot get it to work?
  #13  
Old 12-03-2020, 04:22 PM
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: 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

In what way can't you get it to work?

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]
  #14  
Old 12-04-2020, 02:28 AM
Richystab Richystab is offline Mail Merge Template - remove blank rows in a table Windows 10 Mail Merge Template - remove blank rows in a table Office 2019
Novice
 
Join Date: Dec 2020
Posts: 12
Richystab is on a distinguished road
Default Managed now

Quote:
Originally Posted by macropod View Post
In what way can't you get it to work?

For PC macro installation & usage instructions, see: Installing Macros
For Mac macro installation & usage instructions, see: Word:mac - Install a Macro
I managed in the end using this
Code:
Sub HideRowWhereSecondColumnBlank()
  Dim Pwd As String
  Dim oRow As Integer
  Dim oTable As Table
  Dim iTable As Table
  Dim pState As Boolean
  With ActiveDocument
    pState = False

    If .Tables.Count > 0 Then
      For Each iTable In .Tables
        If iTable.Tables.Count > 0 Then
          For Each oTable In iTable.Tables
            For oRow = oTable.Rows.Count To 1 Step -1
              If Len(Replace(oTable.Cell(oRow, 2).Range.Text, Chr(13) & Chr(7), vbNullString)) = 0 Then
                 On Error Resume Next 'skip vertically merged cells
                 oTable.Rows(oRow).Delete
              End If
            Next oRow
          Next oTable
        End If
      Next iTable
    End If
    If pState = True Then .Protect wdAllowOnlyFormFields, Noreset:=True, Password:=Pwd
      pState = False
    Pwd = ""
  End With
 
End Sub
  #15  
Old 12-05-2020, 03:03 PM
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: 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

That still doesn't tell me anything about your particular problem. In any event, the code you posted is overkill for a problem such as this - much of it is simply irrelevant. Did you actually add the macro in post #12 to your mailmerge main document?

I see you've now started a duplicate thread: https://www.msofficeforums.com/mail-...l-merging.html
Kindly don't do that. In any event the problem as described there is quite different: it's not only column 2 that might be empty.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Closed Thread

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

All times are GMT -7. The time now is 12:57 PM.


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