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?
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