#16
|
|||
|
|||
Thanks Paul,
Problem is i have had to remove some for formatting after adding the new line in. I am a complete novice at VBA (i can paste code into the module) so i am struggling with it. I have attached the new file in the hope you can help and then by reviewing your previous code, understand the errors in my ways |
#17
|
||||
|
||||
Not only have you added new rows, you've deleted one that was there previously. Moving targets are rather hard to code for. Try:
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 InStr(Split(.Cell(1, 1).Range.Text, vbCr)(0), "Total Reward Statement") > 0 Then For r = .Rows.Count To 1 Step -1 If Split(.Cell(r, 1).Range.Text, vbCr)(0) = "Shares Total Reward Statement" Then Exit For Select Case Trim(Split(.Cell(r, 1).Range.Text, vbCr)(0)) Case "PSP Award", "DBP Award", "RSP Award" 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] |
#18
|
|||
|
|||
Thanks again, it looks like it has worked.
Regards |
#19
|
|||
|
|||
More Changes - can you help?
Hi Macropod,
its me again... i have had to make further changes again and those code no longer works, i made previous changes and figures out what was wrong and amended the code, but not this time,. |
#20
|
||||
|
||||
How about we both wait until you decide you want to stop messing with the layout...
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#21
|
|||
|
|||
Trust me.. i wanted this signed off and sorted after your last piece of help.
There are 3 lines of text to go under where it says share ownership guidelines, but thats it! |
#22
|
||||
|
||||
Try:
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 With .Range With .Find .ClearFormatting .Replacement.ClearFormatting .Text = "[DPR][BS]P Award" .Replacement.Text = "" .Forward = True .Wrap = wdFindStop .Format = False .MatchWildcards = True .Execute End With Do While .Find.Found If .InRange(Tbl.Range) = False Then Exit Do If .Cells(1).ColumnIndex = 1 Then r = .Cells(1).RowIndex With Tbl Select Case Trim(Split(.Cell(r, 1).Range.Text, vbCr)(0)) Case "PSP Award", "DBP Award", "RSP Award" 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 End With End If .Collapse wdCollapseEnd .Find.Execute Loop End With End With Next Application.ScreenUpdating = False End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#23
|
|||
|
|||
Mail Merge Table - suppress blank rows
I have a similar issue with an invoice letter mail merge where I have a 5 row table, 1 header row and 4 rows for the invoice lines. Row 2 will always have data in but 3, 4 & 5 may be blank or populated with data. Unfortunately I have no knowledge of VBA and am unsure what I need to do with Paul's sample code. I have created a module in the VBA editor, pasted in Paul's code and saved as a macro enabled file, ran the finish and merge and still got blank rows in the table. Where in the sample code do I add the row numbers that may be blank?
|
#24
|
|||
|
|||
Quote:
I have a similar issue with an invoice letter mail merge where I have a 5 row table, 1 header row and 4 rows for the invoice lines. Row 2 will always have data in but 3, 4 & 5 may be blank or populated with data. Unfortunately I have no knowledge of VBA and am unsure what I need to do with Paul's sample code. I have created a module in the VBA editor, pasted in Paul's code and saved as a macro enabled file, ran the finish and merge and still got blank rows in the table. Where in the sample code do I add the row numbers that may be blank? |
#25
|
||||
|
||||
The macro in the quoted post is for deleting rows where the last column contains only a 0; not for deleting blank rows. To delete rows that are entirely empty, you would use code like:
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 2 Step -1 With .Rows(r) If Len(.Range.Text) = 2 * .Cells.Count + 2 Then .Delete End With Next End With Next End With Application.ScreenUpdating = False End Sub
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#26
|
|||
|
|||
Thank you Paul - to confirm, will this macro run when I use the finish and merge option in the main mail merge (macro enabled doc? I have attached the letter template for info.
Any help appreciated. Ged |
#27
|
||||
|
||||
Yes, it will. Whether you'll get the desired results, though, depends on what's on the rows concerned when there's no data; I suspect your empty 'Invoice Date' and 'Due Date' fields will output dates like 1 Jan 1900, in which case a different macro will be needed.
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#28
|
|||
|
|||
Hi Paul,
I am back again begging for help - A new quandary has happened this year as we have issued a new lot of share awards. The code works amazing to delete all the necessary lines, but this year there are a lot more people who don't have a single award in the entire Award Year and i was wondering if there was a way to delete that whole section if there is no output? |
#29
|
||||
|
||||
What do you mean by 'delete that whole section'?
__________________
Cheers, Paul Edstein [Fmr MS MVP - Word] |
#30
|
|||
|
|||
Sorry, i would describe the section as the "table" that shows the Award i.e 2016 Awards (vesting 2019 etc etc)
The macro you did first time round removes the reference to RSP PSP DBP if there is no data on the line in each of the "sections" but leaves the table in (there were people who had entries in one of the three last year so it wasn't an issue). This year however there area lot of people who don't have an award in certain years and I would ideally like to remove the whole reference to the "Award Year" if the employee has no award, so that there it would just show the years where an award is in place. Does that make sense? |
|
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 |
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 |
How to turn all blank lines into non-blank for legal forms | sieler | Word | 3 | 04-12-2009 01:38 PM |