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:
This is how I would like it to look like doing all the work by hand:
source to share
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
Else
RowEnd = i 'This variable will be used to determine the last merged row
Err.Clear
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.
source to share