I have a file that has data going from row 9-220, with the amount of line varying based on current members. Column A is merged so that 9 and 10 are one cell, 11 and 12 are one cell and so forth. The rest of the columns are not merged so the first row is A9 but contains A9 and A10. After that Column B9 and B10 are not merged and are seperate. The issue is that depending on how many members I currently have sometime the page break falls lets say just for example after row 9 which splits my data. Below is the Macro I have created.

ActiveWindow.View = xlPageBreakPreview
ActiveSheet.ResetAllPageBreaks
Set ActiveSheet.HPageBreaks(1).Location = Range("A36")
Set ActiveSheet.HPageBreaks(2).Location = Range("A71")
Set ActiveSheet.HPageBreaks(3).Location = Range("A139")
Set ActiveSheet.HPageBreaks(4).Location = Range("A139")

I need this modified so that a page break only occurs at above each of the corresponding rows if the cell below contains data. So if cell A37 contains data, then the page will break at 36. The reason that my macro above won't work is that if lets say A37 doesn't have data the macro bombs out on me. If the cell below A36 is blank then I need the macro to stop and pass over the rest. So for example if say the data stops half way through a page lets say at cell A51, then it won't create a page break at A71. It would look something like below, but i don't know how to code it.

ActiveWindow.View = xlPageBreakPreview
ActiveSheet.ResetAllPageBreaks
Set ActiveSheet.HPageBreaks(1).Location = Range("A36")
STOP and pass over remaining lines but begin again after '
Set ActiveSheet.HPageBreaks(2).Location = Range("A71")
Set ActiveSheet.HPageBreaks(3).Location = Range("A139")
Set ActiveSheet.HPageBreaks(4).Location = Range("A139")
'