Removing Lines / Tables with Blank Data

Crosby87 · #16 06-13-2018, 06:24 AM

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

macropod · #17 06-14-2018, 04:25 PM

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

Crosby87 · #18 06-18-2018, 12:33 AM

Thanks again, it looks like it has worked.

Regards

Crosby87 · #19 07-23-2018, 03:15 AM

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,.

macropod · #20 07-23-2018, 05:56 AM

How about we both wait until you decide you want to stop messing with the layout...

Crosby87 · #21 07-23-2018, 07:34 AM

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!

macropod · #22 07-23-2018, 06:06 PM

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

ged147 · #23 11-30-2018, 03:30 AM

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?

ged147 · #24 11-30-2018, 07:05 AM

Quote:

Originally Posted by macropod

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

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?

macropod · #25 11-30-2018, 01:49 PM

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

ged147 · #26 12-01-2018, 02:37 AM

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

macropod · #27 12-01-2018, 12:37 PM

Quote:

Originally Posted by ged147

to confirm, will this macro run when I use the finish and merge option in the main mail merge

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.

Crosby87 · #28 05-30-2019, 07:37 AM

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?

macropod · #29 05-30-2019, 04:49 PM

What do you mean by 'delete that whole section'?

Crosby87 · #30 05-31-2019, 12:13 AM

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?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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