• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
Office 指南

Office 指南

辦公室工作實用教學

  • Excel
  • Word
  • PowerPoint
  • Windows
  • PowerShell
  • R

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

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


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

Excel 資料表格
Excel 資料表格

以下我們介紹幾種常用的 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 的尋找及取代功能。

Excel 尋找及取代
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

分類:Excel 標籤:VBA

讀者互動

發佈留言 取消回覆

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

Primary Sidebar

搜尋

分類

Android Apple C/C++ Excel Linux OneNote PHP PowerPoint PowerShell Python R Windows Word 免費工具 創客 網站架設 線上工具 資料庫 遊戲 雜七雜八

近期文章

  • ITK 以 LabelStatisticsImageFilter 套用遮罩影像計算統計量教學與範例
  • iCloud 匯入 Google 聯絡人教學
  • CSS 檔案最小化與壓縮處理教學與範例
  • Python 使用 ITK 讀取、寫入、建立影像教學與範例
  • Python 以 random 模組產生隨機亂數教學與範例
  • iPhone 手機開啟 VoLTE 與 WiFi 通話 VoWiFi 功能教學
  • Python 以 NumPy 的 unique 函數篩選陣列不重複元素教學與範例
  • ITK 計算 3D 二元遮罩影像物件數量與體積

推薦網站

  • Udemy 線上教學課程
  • Coursera 線上教學課程

關注本站

  • 電子郵件
  • Facebook

Copyright © 2020 · Office Guide