Consulting

Results 1 to 8 of 8

Thread: Delete all empty rows in all tables

  1. #1
    VBAX Newbie
    Joined
    Jan 2018
    Posts
    3
    Location

    Question Delete all empty rows in all tables

    Hi,

    I am using the following macro to delete all rows in all tables of a word document:

    Option Explicit
    
    Public Sub DeleteEmptyRows()
    
    Dim oTable As Table, oRow As Range, oCell As Cell, Counter As Long, _
    NumRows As Long, TextInRow As Boolean
    
    ' Specify which table you want to work on.
    For Each oTable In ActiveDocument.Tables
    ' Set a range variable to the first row's range
    Set oRow = oTable.Rows(1).Range
    NumRows = oTable.Rows.Count
    Application.ScreenUpdating = False
    
    For Counter = 1 To NumRows
    
        StatusBar = "Row " & Counter
        TextInRow = False
    
        For Each oCell In oRow.Rows(1).Cells
            If Len(oCell.Range.Text) > 2 Then
                'end of cell marker is actually 2 characters
                TextInRow = True
                Exit For
            End If
        Next oCell
    
        If TextInRow Then
            Set oRow = oRow.Next(wdRow)
        Else
            oRow.Rows(1).Delete
        End If
    
    Next Counter
    Next oTable
    Application.ScreenUpdating = True
    
    End Sub
    It is working, however, all the columns that have empty rows end up opening very wide.

    Can somebody please give me a hand?


    a.jpg
    Attached Images Attached Images
    • File Type: jpg a.jpg (194.5 KB, 5 views)

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Try:
    Sub DeleteEmptyRowsFromAllTables()
    Application.ScreenUpdating = False
    Dim t As Long, r As Long
    With ActiveDocument
      For t = .Tables.Count To 1 Step -1
        With .Tables(t)
          .AllowAutoFit = False
          For r = .Rows.Count To 1 Step -1
            With .Rows(r)
              If Len(.Range.Text) = .Cells.Count * 2 + 2 Then .Delete
            End With
          Next r
        End With
      Next t
    End With
    Application.ScreenUpdating = True
    End Sub
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    VBAX Newbie
    Joined
    Jan 2018
    Posts
    3
    Location

    Works Perfect. Apply macro before mail merge.

    Thank you very much macropod! That works perfectly.

    One more question. Is there any way the macro can be applied before a mail merge? So each table of all the different destinataries are sent without the empy rows?

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    That suggests you're taking the wrong approach. Your mailmerge should be configured to produce only the rows that are needed. That can be done in one of three ways:
    1. Use of a DATABASE field in the mailmerge main document;
    2. Use of a Directory/Catalog merge; or
    3. Use of a many-to-one addin.

    For 1. see:
    http://answers.microsoft.com/en-us/o...1-1996c14dca5d
    and:
    https://answers.microsoft.com/en-us/...f-8642e46fa103

    For
    2. see:
    http://windowssecrets.com/forums/sho...merge-Tutorial
    or:
    http://www.gmayor.com/Zips/Catalogue%20Mailmerge.zip

    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  5. #5
    Graham Mayor - MS MVP (Word) 2002-2019
    Visit my web site for more programming tips and ready made processes
    http://www.gmayor.com

  6. #6
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    fbucaram: Please read VBA Express' policy on Cross-Posting in item 3 of the rules: http://www.vbaexpress.com/forum/faq...._new_faq_item3
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  7. #7
    VBAX Newbie
    Joined
    Jan 2018
    Posts
    3
    Location

  8. #8
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Also cross-posted at: https://stackoverflow.com/questions/...rom-all-tables
    Kindly provide the cross-post links between *all* of the forums you've done this on.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •