Feb 21st, 2012, 09:12 AM
[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...
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.
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
If i = 10000 Then
MessageBox.Show("The program cannot handle the number of schools selected. Please contact the HelpDesk if you receive this message.")
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?
Click Here to Expand Forum to Full Width