Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-02-2020, 05:45 AM
Richystab Richystab is offline Removing rows when mail merging Windows 10 Removing rows when mail merging Office 2019
Novice
Removing rows when mail merging
 
Join Date: Dec 2020
Posts: 13
Richystab is on a distinguished road
Angry Removing rows when mail merging


I am trying to create labels for students but have all subjects showing. I need to be able to suppress the rows that the pupils do not study. so all blank rows be removed upon merging. Is this possible?
Attached Images
File Type: png merging docs.png (46.5 KB, 9 views)

Last edited by Richystab; 12-02-2020 at 07:32 AM. Reason: attached files
Reply With Quote
  #2  
Old 12-02-2020, 10:04 PM
gmayor's Avatar
gmayor gmayor is offline Removing rows when mail merging Windows 10 Removing rows when mail merging Office 2016
Expert
 
Join Date: Aug 2014
Posts: 3,406
gmayor is a name known to allgmayor is a name known to allgmayor is a name known to allgmayor is a name known to allgmayor is a name known to allgmayor is a name known to all
Default

It would be a simple process to create a macro that checks each row. If you use my merge add-in E-Mail Merge Add-in to merge to separate documents, you could use the following macro to remove them on the fly
Code:
Sub ClearEmpty(oDoc As Document)
Dim oTable As Table
Dim iRow As Integer, iCol As Integer
Dim bFound As Boolean
    With oDoc
        For Each oTable In oDoc.Tables
            If oTable.Columns.Count = 5 Then
                For iRow = oTable.Rows.Count To 2 Step -1
                    bFound = False
                    For iCol = 2 To oTable.Rows(iRow).Cells.Count
                        If Len(oTable.Rows(iRow).Cells(iCol).Range) > 2 Then
                            bFound = True
                            Exit For
                        End If
                    Next iCol
                    If bFound = False Then oTable.Rows(iRow).Delete
                    DoEvents
                Next iRow
            End If
        Next oTable
    End With
End Sub
If you want to run the macro on your already merged document then call it using
Code:
Sub Macro1()
ClearEmpty ActiveDocument
End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 12-05-2020, 03:46 PM
macropod's Avatar
macropod macropod is offline Removing rows when mail merging Windows 10 Removing rows when mail merging Office 2010
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 20,939
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

This thread is a duplicate of a discussion started here: https://www.msofficeforums.com/155991-post12.html, but with relevant detail that was omitted from that discussion.

Try adding the following to the mailmerge main document:
Code:
Sub MailMergeToDoc()
Application.ScreenUpdating = False
Dim Tbl As Table, r As Long, Rng As Range
ActiveDocument.MailMerge.Execute
With ActiveDocument
  For Each Tbl In .Tables
    With Tbl
      For r = .Rows.Count To 2 Step -1
        Set Rng = .Rows(r).Range
        With Rng
          .Start = .Cells(2).Range.Start
          If Len(.Text) = 2 * .Cells.Count + 2 Then .Rows(1).Delete
        End With
      Next
    End With
  Next
End With
Application.ScreenUpdating = False
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #4  
Old 12-07-2020, 03:05 AM
Richystab Richystab is offline Removing rows when mail merging Windows 10 Removing rows when mail merging Office 2019
Novice
Removing rows when mail merging
 
Join Date: Dec 2020
Posts: 13
Richystab is on a distinguished road
Default

Quote:
Originally Posted by macropod View Post
This thread is a duplicate of a discussion started here: https://www.msofficeforums.com/155991-post12.html, but with relevant detail that was omitted from that discussion.

Try adding the following to the mailmerge main document:
Code:
Sub MailMergeToDoc()
Application.ScreenUpdating = False
Dim Tbl As Table, r As Long, Rng As Rangehttps://www.msofficeforums.com/newreply.php?do=newreply&p=156110
ActiveDocument.MailMerge.Execute
With ActiveDocument
  For Each Tbl In .Tables
    With Tbl
      For r = .Rows.Count To 2 Step -1
        Set Rng = .Rows(r).Range
        With Rng
            .Start = .Cells(2).Range.Start
            If Len(.Text) = 2 * .Cells.Count + 2 Then .Rows(1).Delete
        End With
      Next
    End With
  Next
End With
Application.ScreenUpdating = False
End Sub
Hi.
Thankyou. Sorry I'm new to the Forum!
Reply With Quote
Reply

Tags
hide rows

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Removing duplicate rows when identical value in a column ballpoint Excel 1 01-05-2018 08:54 AM
how to delete every blank and non-numeric rows without removing the header enuff Excel 3 08-24-2017 05:56 AM
Removing rows when mail merging Merging Multiple Rows in Mail Merge Letter hrhr3 Mail Merge 4 08-24-2016 07:37 PM
Removing rows when mail merging Merging Rows - HELP please! MessyJessy Excel 7 01-14-2015 01:38 AM
Removing rows when mail merging merging rows and creating sub-rows gib65 Excel 2 12-09-2011 02:09 PM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 11:23 AM.


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