Excel VBA 尋找表格最後一欄、最後一列、最右下角教學與範例

使用 VBA 巨集程式找出 Excel 表格的最後一欄、最後一列或最右下角，判斷有資料的範圍。

在開發 VBA 巨集程式時，判斷 Excel 工作表中有資料的範圍是很常遇到的問題。假設我們有一張 Excel 資料表格如下，該如何使用 VBA 程式自動找出資料的最後一列以及最後一行？

以下我們介紹幾種常用的 VBA 判斷資料範圍方法。

Range.End 函數

Range.End 函數的效果就類似 Ctrl + 方向鍵，下面這段程式碼會從第一行的最底下那一格開始，往上尋找有資料的儲存格：

Dim rowNum As Long
rowNum = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox "最後一列：" & rowNum

而我們就依據這一個儲存格的位置，來判斷資料的最後一列。

執行的結果就會像這樣：

Range.End 函數也可以用來判斷最後一行，概念與用法大同小異，只是方向不同而已：

Dim colNum As Long
colNum = Cells(1, Columns.Count).End(xlToLeft).Column
MsgBox "最後一行：" & colNum

同樣也是根據該儲存格的位置，來判斷資料的最後一行。

執行的結果就會像這樣：

Range.End 函數的優點就是語法簡單、容易理解，不過缺點就是只能根據單一行或單一列來判斷資料範圍，如果資料的範圍不是很整齊（某些儲存格有空缺），用這樣的方式就會有問題。

Range.Find 函數

Range.Find 函數就類似 Excel 的尋找及取代功能。

以下這段 VBA 巨集程式碼會從 A1 儲存格開始往回找（由於 A1 是最左上角的第一格，往回就是從最右下角的那一格），尋找有資料的儲存格：

Dim rowNum As Long

rowNum = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row

MsgBox "最後一列：" & rowNum

以下是這些參數的說明：

• What:="*"：尋找任何有資料的儲存格。
• After:=Range("A1")：從 A1 儲存格之後開始找。
• LookAt:=xlPart：搜尋儲存格內任意部分資料。
• LookIn:=xlFormulas：搜尋公式（就算該公式計算結果是空的也算有資料）。
• SearchOrder:=xlByRows：以列的方向尋找（橫向尋找）。
• SearchDirection:=xlPrevious：往前尋找（由右往左、由下往上）。
• MatchCase:=False：不分大小寫。

雖然 Range.Find 的參數用法有些複雜，不過熟悉了之後，這個函數是很有用的。執行的結果就會像這樣：

Range.Find 判斷最後一行的用法也差不多：

Dim colNum As Long

colNum = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByColumns, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Column

MsgBox "最後一行：" & colNum

執行的結果就會像這樣：

Range.Find 函數的優點就是會搜尋整個列或行，就算資料不整齊也不會漏掉，缺點就是參數太複雜，需要時間熟悉用法。

Range.SpecialCells 函數

Range.SpecialCells 函數的功能就類似 Ctrl + End 快速鍵，馬上跳到表格的最右下方，不過 Range.SpecialCells 函數在判斷資料範圍時，會尋找「有使用過」的儲存格（即使它現在是空的），這一點跟上面的兩種方式不同。

以下是使用 Range.SpecialCells 函數找出表格最右下方儲存格的範例：

With Range("A1").SpecialCells(xlCellTypeLastCell)
  MsgBox "最後一列：" & .Row & vbNewLine & _
      "最後一行：" & .Column
End With

Range.SpecialCells 會直接找出資料最右下角的儲存格，所以我們可以同時得到資料的最後一列與最後一行。

執行的結果會像這樣：

這是直接輸出右下角儲存格位址的範例：

Dim lastCell As Range
Set lastCell = Range("A1").SpecialCells(xlCellTypeLastCell)
MsgBox "最右下角：" & lastCell.Address

這是執行的結果：

Range.SpecialCells 函數可用來找出有使用過的儲存格範圍，這個在清理工作表時很有用，不過它找出的範圍不一定都是有資料的（這個範圍只有在活頁部存檔時才會重設）。

參考資料：Excel Campus