## [Excel] Finding Row Closest to Total Height/Pixels From Top

Office Version: 2010
Code: VB.NET w/ Excel Interop

I'm trying to find the best way to find out what row is closest to a specific total height from the top of the excel worksheet. The code I have now works, but it seems needlessly time consuming. I figured there has to be a better way but I haven't found one yet. Here is what I'm doing now...

Code:
```Public Function NewBreakLocation(xTotalHeight As Double, xWS As xExcel.Worksheet) 'Based on xTotalHeight this returns the row that occurs right after it so we know where to put a page break.

Dim xBreakRow As Integer
Dim xHeightCounter As Double = 0
Dim xCell As xExcel.Range

For i = 1 To 10000

xCell = xWS.Cells(i, 1)

If xCell.Top > xTotalHeight Then
xBreakRow = i
Exit For
End If

If i = 10000 Then
Return 0
End If

Next

Return xBreakRow

End Function```
So basically what this function does is it checks the top of every row until it finds one greater than the specific total height I'm looking for. Then it returns the row number so I know what row to put a page break in. The reason I'm doing this is because I have an unknown number of charts that will be created and each needs to be on its own page. So I keep track of the total distance from the top each time I add a chart and any buffer space.

But since the charts hover over the cells I can't use anything like "xlCellTypeLastCell".

So after every single chart I add, which could be as many as 30 or more depending, I have to start from the bottom and check all the rows height again. Repeat this over and over and you've got a lot of waste.

Anybody know a better way to find out what row is near a specific distance from the top?