Prevent vertical merging of cells from breaking across the entire page - automatically

I need to create documents where large data tables are copied to them from Excel. Tables can be hundreds of lines long and are typically ~ 20 columns wide. Many columns are vertically combined to improve readability and group datasets.

I was able to write a macro that completely formats the entire table, except that I was unable to figure out how to automatically prevent switching / splitting vertically linked cells across multiple pages. To do this manually, you select all but the last line in the merge and then enable "Keep With Next" in the paragraph settings. I thought it would be easy to do, but you cannot access individual rows in VBA if there are vertically concatenated cells in the table.

Does anyone have an idea how to automatically step through the lines and set the "Keep With Next" property for groups of lines that have been concatenated together?

Here's an example of how Word typically handles vertically merged cells across tables: Here is an example of how Word typically handles vertically merged cells in tables

This is how I would like it to look like doing all the work by hand: This is what I would like to look like doing all the work by hand


source to share

1 answer

Yes, working with merged cells in Word (and Excel for that matter) is quite annoying.

This can be done, however, by accessing individual cells in the table. I wrote the following subroutine below that should work for you. I assumed that you have at least one column that does not have vertically merged cells, and that you only have one column that controls the length of the merged block. Adding more control columns should be easy though.

Sub MergedWithNext() 'FTable As Table)

Dim Tester As String
Dim FTable As Table
Dim i As Integer
Dim imax As Integer
Dim RowStart As Integer
Dim RowEnd As Integer
Dim CNMerged As Integer
Dim CNNotMerged As Integer
Dim CNMax As Integer

CNMerged = 2 'A column number that is vertically merged that you don't want to split pages
CNNotMerged = 1 'A column number that has no vertical mergers

Set FTable = Selection.Tables(1)

With FTable
imax = .Rows.Count
CNMax = .Columns.Count

'Start with no rows kept with next
ActiveDocument.Range(Start:=.Cell(1, 1).Range.Start, _
    End:=.Cell(imax, CNMax).Range.End).ParagraphFormat.KeepWithNext = False

On Error Resume Next
For i = 2 To imax 'Assume table has header

    Tester = .Cell(i, CNMerged).Range.Text 'Test to see if cell exists
    If Err.Number = 0 Then 'Only the first row in the merged cell will exist, others will not

        'If you are back in this If statement, then you have left the previous block of rows
        'even if that was a block of one. The next If statement checks to see if the previous
        'row block had more than one row. If so it applies the "KeepWithNext" property

        If (RowEnd = (i - 1)) Then

            '.Cell(RowStart, 1).Range.ParagraphFormat.KeepWithNext = True
            ActiveDocument.Range(Start:=.Cell(RowStart, CNNotMerged).Range.Start, _
                End:=.Cell(RowEnd - 1, CNNotMerged).Range.End).ParagraphFormat.KeepWithNext = True

                'Use RowEnd - 1 because you don't care if the whole merged block stays with the next
                'row that is not part of the merger block

        End If

        RowStart = i 'Beginning of a possible merger block
        RowEnd = 0 'Reset to 0, not really needed, used for clarity


        RowEnd = i 'This variable will be used to determine the last merged row

    End If

    If i = imax Then 'Last Row

        If (RowStart <> imax) Then

            ActiveDocument.Range(Start:=.Cell(RowStart, CNNotMerged).Range.Start, _
                End:=.Cell(imax - 1, CNNotMerged).Range.End).ParagraphFormat.KeepWithNext = True

                'Use imax - 1 because you don't care if the whole merged block stays with the next
                'row that is not part of the merger block

        End If

    End If

Next i
On Error GoTo 0
End With
End Sub


This code will loop through every row in the table, excluding the header, looking for vertically concatenated cells. After it finds the block, it will assign the "Keep With Next" properties to every line in the block, except for the last line.



All Articles