Edison.X. Blog

     取得包含 E2 儲存格之整塊範圍, 空白將不計
    ' MsgBox Range("E2").CurrentRegion.Address ' $D$2:$H$4
   
    ' 取得儲存格整塊範圍, 空白也計
    Const FirstCell = "D3" ' 整筆資料第一個儲存格
    Const LastCell = "D65536" ' 整個 excel 最後一個儲存格
    Dim myRow, myCol As Integer
    Dim tib, rng As Range
   
    myRow = Range(LastCell).End(xlUp).Row
    myCol = Range(FirstCell).End(xlToRight).Column
    Range(Range(FirstCell), Cells(myRow, myCol)).Select
   
    ' 取得除了輸入公式外之資料範圍
    myRow = Cells.SpecialCells(xlCellTypeLastCell).Row
    myCol = Range(FirstCell).End(xlToRight).Column
    
    Set tib = Range(Range(FirstCell), Cells(myRow, myCol))
        
    Set rng = tib.Find("*", _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        Searchorder:=xlByRows, _
                        Searchdirection:=xlPrevious)
    ' 搜尋外表有東西的儲存格
    Set tib = Range(Range(FirstCell), Cells(rng.Row, myCol))
    MsgBox tib.Address