Adjusting Page Break in Excel (Keep Together)

[sxschech]

Presently I have hardcoded the page break in vba, however, was curious if this could be done more flexibly as number of cols could grow or shrink in the future. The layout is gender by ethnicity and would like to avoid breaking between genders.

How it Should look
[tt]
Group1 Group2 | Group3
M F M F | M F
|
[/tt]

How it Looks before adjustment
[tt]
Group1 Group2 Gro|up3
M F M F M | F
|
[/tt]


Code Snippet currently using

ActiveWindow.View = xlPageBreakPreview
Set ActiveSheet.VPageBreaks(1).Location = Range("P1")
ActiveWindow.View = xlNormalView

Is there some logic that could find the default page breaks and then decide if the M's are on one page and the F's another, to adjust accordingly?

 

[CautionMP]

Expanding on your snipit and assuuming the following worksheet structure:[tt]
| A | B | C | D | E | F |
--|-----|-----|-----|-----|-----|-----|
1 | GROUP 1 | GROUP 2 | GROUP 3 |
--|-----|-----|-----|-----|-----|-----|
2 | m | f | m | f | m | f |[/tt]

If the vertical page break falls in a column where the second row value is "f", the page break needs to move one column to the left.

Sub AdjustPageBreak()
Dim VerticalBreak As VPageBreak
Dim RangeHeader As Range
ActiveWindow.View = xlPageBreakPreview
For Each VerticalBreak In ActiveSheet.VPageBreaks
  'VerticalBreak.Location returns row 1, need to add 1
  'to get the m/f header row
  If VerticalBreak.Location.Offset(1).Value = "[b]f[/b]" Then
    'Offset the column 1 to the left
    Set VerticalBreak.Location = VerticalBreak.Location.Offset(, -1)
  End If
Next VerticalBreak
ActiveWindow.View = xlNormalView
End Sub

Hope this helps,
CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[sxschech]

Thanks CautionMP, looks like it will work for me.