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
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!!!
For row = 1 To MyRange.Rows.Count
For col = 1 To MyRange.Columns.Count
If row - MaxRow > 15 Then
Next rowIf CStr(MyRange.Cells.Item(row, col).value2) <> "" Then
If FoundData And CStr(MyRange.Cells.Item(row, col).value2) = "" Then
If col - MaxCol > 15 Then
Next colFoundData = True
End If
If FoundData And CStr(MyRange.Cells.Item(row, col).value2) = "" Then
If row > MaxRow Then
If col > (MaxCol - 1) Then
FoundData = False
End IfMaxRow = row
End If
If col > (MaxCol - 1) Then
MaxCol = col - 1
End If
FoundData = False
If col - MaxCol > 15 Then
Exit For
End If
If row - MaxRow > 15 Then
Exit For
End If
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!!!