Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-31-2018, 04:41 AM
Crosby87 Crosby87 is offline Removing Lines / Tables with Blank Data Windows 7 64bit Removing Lines / Tables with Blank Data Office 2013
Novice
Removing Lines / Tables with Blank Data
 
Join Date: May 2018
Posts: 16
Crosby87 is on a distinguished road
Default Removing Lines / Tables with Blank Data

HI All,



I am trying to create a mail merge that shows details of share awards of 3 years (2 Awards in each year). For ease i want to keep the template standard as this will be used year after year and data just updated over/written.

The problem i have is that not all recipients of an award this year (the start point) received awards in previosu years, meaning some have multiple lines of blank data.

I have uploaded my dummy data which shows no award for the DBP in 2018, but thsi may occur in the other tables above to...any help is appreciated
Attached Images
File Type: jpg Capture.JPG (72.8 KB, 75 views)
Reply With Quote
  #2  
Old 05-31-2018, 06:26 AM
macropod's Avatar
macropod macropod is offline Removing Lines / Tables with Blank Data Windows 7 64bit Removing Lines / Tables with Blank Data 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

The only ways around this are to:
• have a different version of each table for the options that may or may not occur and use IF tests to determine which version of the table to output in each case; or
• use a macro to clean up the output post-merge.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #3  
Old 05-31-2018, 07:27 AM
Crosby87 Crosby87 is offline Removing Lines / Tables with Blank Data Windows 7 64bit Removing Lines / Tables with Blank Data Office 2013
Novice
Removing Lines / Tables with Blank Data
 
Join Date: May 2018
Posts: 16
Crosby87 is on a distinguished road
Default

I was hoping for the latter but have no idea where to start..
Reply With Quote
  #4  
Old 05-31-2018, 04:43 PM
macropod's Avatar
macropod macropod is offline Removing Lines / Tables with Blank Data Windows 7 64bit Removing Lines / Tables with Blank Data 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

You could add 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 last cell on a given row is empty.
Code:
Sub MailMergeToDoc()
Application.ScreenUpdating = False
Dim Tbl As Table, r As Long, c As Long
ActiveDocument.MailMerge.Execute
With ActiveDocument
  For Each Tbl In .Tables
    With Tbl
      c = .Columns.Count
      For r = .Rows.Count To 2 Step -1
        If Split(.Cell(r, c).Range.Text, vbCr) = 0 Then .Rows(r).Delete
      Next
    End With
  Next
End With
Application.ScreenUpdating = False
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #5  
Old 06-01-2018, 12:17 AM
Crosby87 Crosby87 is offline Removing Lines / Tables with Blank Data Windows 7 64bit Removing Lines / Tables with Blank Data Office 2013
Novice
Removing Lines / Tables with Blank Data
 
Join Date: May 2018
Posts: 16
Crosby87 is on a distinguished road
Default

i have ran it but it has deleted all the data out of the workbooks. I am guessing there are multiple tables with multiple blank lines in my book (i made it in excel and then pasted to Word)
Reply With Quote
  #6  
Old 06-01-2018, 12:24 AM
macropod's Avatar
macropod macropod is offline Removing Lines / Tables with Blank Data Windows 7 64bit Removing Lines / Tables with Blank Data 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

It's a Word macro that has no effect on any workbooks - only on tables in the mailmerge output document and, even then, only on rows in which the last cell is empty, but always leaving the header row alone.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 06-01-2018, 07:21 AM
Crosby87 Crosby87 is offline Removing Lines / Tables with Blank Data Windows 7 64bit Removing Lines / Tables with Blank Data Office 2013
Novice
Removing Lines / Tables with Blank Data
 
Join Date: May 2018
Posts: 16
Crosby87 is on a distinguished road
Default

Sorry i meant the data pulled from my workbooks. the output document is one giant table (I think) as i created it in excel and then pasted to word)
Reply With Quote
  #8  
Old 06-01-2018, 03:01 PM
macropod's Avatar
macropod macropod is offline Removing Lines / Tables with Blank Data Windows 7 64bit Removing Lines / Tables with Blank Data 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

Perhaps you could attach your mailmerge main document and a sample output document to a post with some representative data (delete anything sensitive)? You can do this via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #9  
Old 06-08-2018, 04:45 AM
Crosby87 Crosby87 is offline Removing Lines / Tables with Blank Data Windows 7 64bit Removing Lines / Tables with Blank Data Office 2013
Novice
Removing Lines / Tables with Blank Data
 
Join Date: May 2018
Posts: 16
Crosby87 is on a distinguished road
Default Removing Lines / Tables with Blank Data Part 2

Hi Following on from my last post.

MacroPod has asked for the attached to hopefully be able to help out.

I have removed a cover page from the first page that gave away the company!

I have made up most of the data but the concept will be the same and there is about 74 people in the merge
Attached Files
File Type: docx Total Reward Statement.docx (29.8 KB, 25 views)
File Type: xlsx Total Rem Statements Mail Merge3.xlsx (13.5 KB, 16 views)
Reply With Quote
  #10  
Old 06-08-2018, 07:25 PM
macropod's Avatar
macropod macropod is offline Removing Lines / Tables with Blank Data Windows 7 64bit Removing Lines / Tables with Blank Data 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

The reason the macro has trouble with your document is that the content is not in separate tables and you have many rows whose last cell is empty and that has nothing to do with the rows you want to delete. Moreover, you table has vertically merged cells, which means it's impossible to process it in the normal manner. None of that was apparent from your screenshot.

Try the following revision to the macro:
Code:
Sub MailMergeToDoc()
Application.ScreenUpdating = False
Dim Tbl As Table, r As Long, c As Long
ActiveDocument.MailMerge.Execute
For Each Tbl In ActiveDocument.Tables
  With Tbl
    If .Range.Cells(.Range.Cells.Count).RowIndex = 50 Then
      For r = 43 To 30 Step -1
        Select Case r
          Case 30, 31, 36, 37, 42, 43
            If Split(.Cell(r, 4).Range.Text, vbCr)(0) = "" Then
              For c = 4 To 1 Step -1
                .Cell(r, c).Delete
              Next
            End If
        End Select
      Next
    End If
  End With
Next
Application.ScreenUpdating = False
End Sub
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #11  
Old 06-12-2018, 05:07 AM
Crosby87 Crosby87 is offline Removing Lines / Tables with Blank Data Windows 7 64bit Removing Lines / Tables with Blank Data Office 2013
Novice
Removing Lines / Tables with Blank Data
 
Join Date: May 2018
Posts: 16
Crosby87 is on a distinguished road
Default

Thank you so much for your help (and apologies for being really poor at explaining myself).

I have been asked to slightly change the format by adding in another line underneath the DBP award line.

can the code easitly be amended to reflect this?
Reply With Quote
  #12  
Old 06-12-2018, 05:17 AM
macropod's Avatar
macropod macropod is offline Removing Lines / Tables with Blank Data Windows 7 64bit Removing Lines / Tables with Blank Data 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

Yes - you simply need to update the total row count that is used to identify the table (i.e. RowIndex = 50) and the individual row references for the rows you might want to delete (i.e. Case 30, 31, 36, 37, 42, 43).
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #13  
Old 06-12-2018, 05:44 AM
Crosby87 Crosby87 is offline Removing Lines / Tables with Blank Data Windows 7 64bit Removing Lines / Tables with Blank Data Office 2013
Novice
Removing Lines / Tables with Blank Data
 
Join Date: May 2018
Posts: 16
Crosby87 is on a distinguished road
Default

...for the dumb one in the thread....i take it i highlight the workbook to the row i want, go into table properties and start the counts from there?
Reply With Quote
  #14  
Old 06-12-2018, 06:04 AM
Crosby87 Crosby87 is offline Removing Lines / Tables with Blank Data Windows 7 64bit Removing Lines / Tables with Blank Data Office 2013
Novice
Removing Lines / Tables with Blank Data
 
Join Date: May 2018
Posts: 16
Crosby87 is on a distinguished road
Default

I have had a stab but its not worked (It worked the first time around so thank you).
Reply With Quote
  #15  
Old 06-13-2018, 03:03 AM
macropod's Avatar
macropod macropod is offline Removing Lines / Tables with Blank Data Windows 7 64bit Removing Lines / Tables with Blank Data 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

Clearly, you'd increase the RowIndex value by the number of rows you're adding to the table.
As for the Case values, that likewise depends on which of the DBP award lines (you have 3) you're adding the new line below. If it's all 3, you might use:
Case 30, 31, 32, 37, 38, 39, 44, 45, 46
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to delete every blank and non-numeric rows without removing the header enuff Excel 3 08-24-2017 05:56 AM
Removing blank pages at the end of a document Xanzia Word 3 06-01-2014 11:13 AM
Removing Lines / Tables with Blank Data Getting blank lines instead of supressed lines. Welshie82 Mail Merge 2 11-14-2011 01:41 AM
Urgent help needed with removing blank lines... iammom2four Outlook 0 12-15-2010 06:12 AM
Removing Lines / Tables with Blank Data How to turn all blank lines into non-blank for legal forms sieler Word 3 04-12-2009 01:38 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:02 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