vba - Prevent Vertically Merged Cells from Breaking Across Page - Automatically -

i have create documents have large tables of data copied them excel. tables can hundreds of rows long , ~20 columns wide. many of columns have been merged vertically enhance readability , group sets of data.

i have been able write macro format entire table, except have not been able figure out how automatically prevent vertically merged cells breaking/splitting across multiple pages. manually, select of rows in merger except last 1 , turn on "keep next" in paragraph settings. thought easy do, can not access individual rows in vba if there vertically merged cells in table.

does have idea how automatically go through rows , set "keep next" property groups of rows have been merged together?

here example of how word handles vertically merged cells across tables:

this how look, doing work manually:

yes, working merged cells in word (and excel matter) quite annoying.

this can done, though, accessing individual cells in table. have written following sub routine below should work you. assumed had @ least 1 column no vertically merged cells in , had 1 column controlled length of merged block. although adding more controlling columns should easy.

sub mergedwithnext() 'ftable table)  dim tester string dim ftable table dim integer dim imax integer dim rowstart integer dim rowend integer dim cnmerged integer dim cnnotmerged integer dim cnmax integer  cnmerged = 2 'a column number vertically merged don't want split pages cnnotmerged = 1 'a column number has no vertical mergers  set ftable = selection.tables(1)  ftable imax = .rows.count cnmax = .columns.count  'start no rows kept next activedocument.range(start:=.cell(1, 1).range.start, _     end:=.cell(imax, cnmax).range.end).paragraphformat.keepwithnext = false  on error resume next = 2 imax 'assume table has header      tester = .cell(i, cnmerged).range.text 'test see if cell exists     if err.number = 0 'only first row in merged cell exist, others not          'if in if statement, have left previous block of rows         'even if block of one. next if statement checks see if previous         'row block had more 1 row. if applies "keepwithnext" property          if (rowend = (i - 1))              '.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 don't care if whole merged block stays next                 'row not part of merger block          end if          rowstart = 'beginning of possible merger block         rowend = 0 'reset 0, not needed, used clarity      else          rowend = 'this variable used determine last merged row         err.clear      end if      if = imax 'last row          if (rowstart <> imax)              activedocument.range(start:=.cell(rowstart, cnnotmerged).range.start, _                 end:=.cell(imax - 1, cnnotmerged).range.end).paragraphformat.keepwithnext = true                  'use imax - 1 because don't care if whole merged block stays next                 'row not part of merger block          end if      end if  next on error goto 0 end end sub 

this code loop through each row in table, excluding header, looking vertically merged cells. once finds block, assign "keep next" property each row in block, except last row.