Optimisation Required - Tech Heads to the Resue!!

[lucrosus]

I am trying to find the quickest way in code to find the bounds of a spreadsheet contents. This is my current approach: -

For row = 1 To MyRange.Rows.Count

For col = 1 To MyRange.Columns.Count

If CStr(MyRange.Cells.Item(row, col).value2) <> "" Then

FoundData = True

​

End If

If FoundData And CStr(MyRange.Cells.Item(row, col).value2) = "" Then

If row > MaxRow Then

MaxRow = row

​

End If

If col > (MaxCol - 1) Then

MaxCol = col - 1

​

End If

FoundData = False

​

End If

If col - MaxCol > 15 Then

Exit For

​

End If

​

Next col

If row - MaxRow > 15 Then

Exit For

​

End If

​

Next row




I have used an arbitary value of 15 blank columns or rows to signify the end of the content (to try and speed things up). It is still too slow on the big sheets mind.

Surely there must be a function for this? Strange, because if you are using Excel and press Control + Shift + End it finds the bounds of the sheet automatically!

Come on you tech-heads - who can better this and give me the quickest approach!!!