VB2008, using HPageBreaks to find Excel page break gives wrong answer

[plocks]

I understand HPageBreaks gives you the last page break available, but if there is only one sheet it won't give you a page break. My fix for this is to keep entering data into row after row of my sheet until a new page break occurs, then grab that row.

The problem is that HPageBreaks keeps telling me my page break is at, lets say, A30. When in reality its at A44. I've tried HPageBreaks a couple different ways but it keeps finding row 30 and not row 44.

I think the reason may be that my Excel document has taller row heights, because if I put my code in a program where the Excel row heights are all normal, then it works perfectly.

I do not know how to fix this so HPageResult will find the correct page break row, no matter how tall my rows are.

Code below:

        'i has my last row of data.
        i += 2
        Dim iStart As Integer = i 'hold this row so we can clear what we are about to do
        Dim pgBreaks As Integer = oSheet1.HPageBreaks.Count 'current # of page breaks
        Do Until oSheet1.HPageBreaks.Count > pgBreaks
            'Keep adding a row of data until we reach a page break
            oSheet1.Range("A" & i).Value = "fill"
            i += 1
        Loop
        oSheet1.Range("A" & iStart & ":A" & i).ClearContents() 'get rid of all the "fill"s you just inserted
        Dim LastRow As Integer = i - 2 'we will be in the second row of the new page, so move up
        i = LastRow

 

[SkipVought]

hi,

understand HPageBreaks gives you the last page break available

HPageBreaks is a collection of horizontal page breaks on a sheet.

Why do you need to find the next non-existant page?

Don't understand why this is necessary???



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[plocks]

My end game is to have a "signature" line printed at the very bottom of the last page.

It would like like this:
Signature_______________________________________

To do this I need to find the last row on the last page.

So I was going to use HPageBreaks to find it. But it won't give me the last row on the last page because technically there is no break point there. So my code above creates a new page break by entering data into each row until a page break appears, tells me which row its on, and then clears all the junk data I entered.

 

[SkipVought]

That is an entirely different issue that has nothing to do with any kind of page break.

    With oSheet1
        With .Cells(.UsedRange.Row + .UsedRange.Rows.Count, 1)
            .Value = "Signature"
            With .Offset(0, 1).Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlThin
            End With
        End With
    End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[plocks]

Skip,

That code is placing the Signature line right after the last row that contains data. I need it to go on the last row of the printable page.

 

[SkipVought]

in a loop, I'd increase the RowHeight incrimentally, until the Count of HPageBreaks increases.

Then subtract one incriment.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]