Perhaps something along the lines of
Code:
Sub testing()
Dim lastRow As Long, lastColumn As Long, desiredRow As Long
Dim ws As Worksheet, rRange As Range, myarray2 As Variant
Set ws = ActiveSheet '<<< alter to suit
lastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
lastColumn = ws.Cells.SpecialCells(xlCellTypeLastCell).Column
desiredRow = Range(Cells(1, 1), Cells(lastRow - 1, lastColumn)).Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Set rRange = Range(Cells(1, 1), Cells(desiredRow, lastColumn))
myarray2 = rRange
MsgBox "First dimension (rows)" & vbLf & _
" lower bound is " & LBound(myarray2, 1) & vbLf & _
" upper bound is " & UBound(myarray2, 1) & vbLf & vbLf & _
"Second dimension (columns)" & vbLf & _
" lower bound is " & LBound(myarray2, 2) & vbLf & _
" upper bound is " & UBound(myarray2, 2)
End Sub