VBA Control Of Page Breaks

[DannyDont]

I am tracking rows in my worksheet and inserting a horizontal page break when the next "block" of data happens to spill over into another page. I track the cell before the "block" is entered so I know where I want to set a page break. This all seems to work fine until I hit the situation where an Excel-generated page break is on the bottom of the row that I want to set a top-of-the-row page break. It ignores the command that was used several times before hitting this situation. Any subsequent attempts to enter page breaks are ignored as well. However, when I go back and look at my spreadsheet, page breaks have been inserted in rows that I had already passed by.
Here is the code that is being used. ??? Where do I enter the code?????

Sub UTY_Count_Page_Breaks()
Stop
' ActiveSheet.Range("AA" & ActiveSheet.UsedRange.Rows.Count) = ""
ActiveSheet.Range("A" & ActiveSheet.UsedRange.Rows.Count).Select
' MsgBox ActiveSheet.HPageBreaks.Count 'Count For Next on HPageBreaks.Count
MsgBox CountOfBreaks & "/" & ActiveSheet.HPageBreaks.Count
If ActiveSheet.HPageBreaks.Count > 0 Then
' The following command is not working. Previously I could display the address
' ActiveSheet.HPageBreaks(ActiveSheet.HPageBreaks.Count).Location.Address 'Gives Range/Address of PageBreak 1 - But is not working at the moment.
End If
If ActiveSheet.HPageBreaks.Count > CountOfBreaks Then
CountOfBreaks = ActiveSheet.HPageBreaks.Count
Debug.Print Control_Last_Logical_PageBreak
ActiveSheet.HPageBreaks.Add before:=Rows(Int(Right(Control_Last_Logical_PageBreak, 2)))
' CountOfBreaks = CountOfBreaks + 1
End If
End Sub

I hope that I did that correctly.

Control_Last_Logical_PageBreak contains the address of the last row when I would want to logically insert a page break. I have come through 25 logical page breaks while building the worksheet, reading from a CSV file opened within the app. The VBA Code tracks the proper row address correctly each time. During that time I have had 2 or 3 successful page break insertions whenever the ActiveSheet.HPageBreaks.Count exceeds the CountOfBreaks.

I am not sure that I have explained this so that it can be understood, but I hope so. I went through several pages about page breaks but did not find anything that seemed to fit my situation. Any help would be greatly appreciated.

Note: I did not go back through the already processed data to see if a page break was inserted elsewhere in the document when the command failed after it initially failed where there was a bottom page break on the same row that I attempted to set a top page break.

I added a snippet of the spreadsheet. There are two incorrectly placed page breaks in the image. Only the page break at the top of Row 44 is valid (or what I expect) The other two came from"out of the blue" but probably were added when the insert page break began failing. As the image shows, line 44 and line 45 page breaks create a blank page and 41 and 43 create a page with little information on the page.

Excel added the break between 144 and 145. I attempted to set a top-of-row break but as you can see it did not take. I do not know if this page break is top-of-row or bottom-of-row.

After that initial failure, the insert page break no longer functioned properly. I attempted to set the top-of-row page break on line 192. As you can see, I was not successful.

Thanks for your help,
Dan Foltz

 

[DannyDont]

I have done some additional testing this morning. It appears that the command that I am using does not like rows greater than 100. When I get the current cell address it is returned in $A$24 format. I stopped the program execution whenever the page counts (system vs mine) were different. The first failure came at $A$144. It actually set a page break at A44. This just happened to be where I had already inserted a page break, so it did not show up as an error, Since I now stop execution at this point I was able to modify the address variable. The first try was at $A$26. It worked. Next, I tried $A$99 and it worked. Next, I tried $A$146 and it added a page break at A46. I then tried removing the $ in the address and A146. It did not insert a page break at that point. It inserted a page break at A46.

Here are my field definitions. Perhaps one of them is incorrect.

ActiveSheet.HPageBreaks.Count - this is a system value
Dim CountOfBreaks as Long (Changed to Integer)
Dim Control_Last_Logical_PageBreak As Variant (Changed to String)

I will change the Long to Integer and see what happens. This did not change the result
Now I will change variant to string. This did not change the result
I am at a loss.

Any thoughts are appreciated.
Thanks,
DannyDont

 

[DannyDont]

I found a different way to set page breaks and it seems to have resolved the issue. I was using:
ActiveSheet.HPageBreaks.Add before:=Rows(Int(Right(Control_Last_Logical_PageBreak, 2)))
And am now using:
Worksheets(Control_Tab_Name).HPageBreaks.Add Before:=Worksheets(Control_Tab_Name).Rows(Row_Page_Break)
Where Control_Tab_Name is an added tab with a tab name created from information in the input file.
The Row_Page_Break is the row of the last logical row for a page break.

It was tested up to row 184 and worked just fine.
So if I knew how to close this issue I would do so.

I don't see the mark for the solution icon.