Excel巨集範例：8組簡短強大的VBA程式代碼

Excel巨集範例可以將日常工作自動化執行，涵蓋清空資料、刪除空白列、工作表管理與格式調整等，本文分享8組簡短強大的程式代碼，帶你體驗VBA的好處。

📊 【Excel VBA 懶人包】

8 個超實用「一行巨集」，解放你的雙手！

日常維護報表總是被重複的瑣碎動作吃掉時間嗎？其實，只要利用幾段簡短的 VBA 程式碼，就能讓效率翻倍！今天分享 8 個超強大的「微型巨集」，幫你省下大把青春。

資料清理

1️⃣ 清空資料，完美保留公式

痛點： 更新報表，手動清除舊數據超怕誤刪公式。
解法： 只清除「常數」，公式原封不動。

Sub 清空資料保留公式()
  On Error Resume Next: Selection.SpecialCells(xlCellTypeConstants).ClearContents: On Error GoTo 0
End Sub

💡 贊贊提醒： 執行前請先「選取範圍」。On Error 是最好的防呆，全公式也不報錯。

資料清理

2️⃣ 一鍵秒殺 A 欄空白列

痛點： 系統匯出資料夾雜空白列，逐一刪除容易刪錯。
解法： 鎖定 A 欄，找出空白格後一次性整列刪除。

Sub 刪除A欄空白列()
  On Error Resume Next: Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete: On Error GoTo 0
End Sub

工作表管理

3️⃣ 自動新增日期工作表

痛點： 每天做日報表，手動新增還要慢慢敲日期。
解法： 在最後方新增工作表，自動命名為 yyyymmdd。

Sub 新增日期工作表()
  Sheets.Add(After:=Sheets(Sheets.Count)).Name = Format(Date, "yyyymmdd")
End Sub

💡 贊贊提醒： 同一天按兩次會因為名稱重複報錯喔！適合每天只執行一次。

工作表管理

4️⃣ 生成工作表目錄超連結

痛點： 活頁簿裡幾十張表，找資料像在大海撈針。
解法： 在第一張表自動建立超連結目錄，點一下飛過去。

Sub 工作表目錄()
  Dim ws As Worksheet: With Sheets(1): .Cells.Clear: .[A1] = "工作表目錄": For Each ws In Worksheets: If ws.Name <> .Name Then .Hyperlinks.Add .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row + 1, 1), "", "'" & ws.Name & "'!A1", , ws.Name: End If: Next: End With
End Sub

版面調整

5️⃣ 自動適配欄寬與列高

痛點： 貼上外部資料後，欄寬不夠出現 ####。
解法： 全表自動展開（AutoFit），版面瞬間變整齊。

Sub 自動調整欄寬列高()
  Cells.EntireColumn.AutoFit: Cells.EntireRow.AutoFit
End Sub

版面調整

6️⃣ 一鍵消滅全表合併儲存格

痛點： 合併儲存格不能排序、篩選，是整理資料的噩夢。
解法： 一鍵打回原形，全面取消合併。

Sub 取消合併()
  Cells.UnMerge
End Sub

視窗檢視

7️⃣ 隨心所欲重新凍結窗格

痛點： 換地方凍結視窗，每次都要先取消再重新設定。
解法： 根據現在選取的儲存格，一鍵「解除並重新凍結」。

Sub 凍結窗格()
  ActiveWindow.FreezePanes = False: ActiveWindow.FreezePanes = True
End Sub

檔案匯出

8️⃣ 一鍵匯出 PDF 丟到桌面

痛點： 報表轉 PDF 總是要另存新檔、選格式、找桌面…
解法： 自動抓取桌面路徑，並以工作表名稱存成 PDF。

Sub 匯出PDF到桌面()
  ActiveSheet.ExportAsFixedFormat xlTypePDF, CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & ActiveSheet.Name & ".pdf"
End Sub

💡 贊贊提醒： 這是 Windows 專屬寫法！Mac 蘋果用戶的話，路徑抓取方式會不太一樣喔。

一、清空資料保留公式

在日常維護報表時，經常需要清除上一期輸入的數值，但又希望保留儲存格中的公式與格式。如果手動逐一辨識，很容易誤刪公式。這支巨集利用SpecialCells(xlCellTypeConstants)精準鎖定常數儲存格，只清除文字、數值、日期等人工輸入的內容，公式則完全保留。

Sub 清空資料保留公式()
    On Error Resume Next: Selection.SpecialCells(xlCellTypeConstants).ClearContents: On Error GoTo 0
End Sub

執行前先選取要清空的範圍，巨集會在選取區域內進行作業。On Error Resume Next的用途是避免當範圍內全為公式時，SpecialCells找不到常數儲存格而跳出錯誤訊息。

二、一鍵刪除A欄空白列

從系統匯出的資料或人工整理的清單，常常會夾雜空白列。若逐列手動刪除，不但費時，也容易遺漏。這支巨集對整個A欄執行SpecialCells(xlCellTypeBlanks)找出所有空白儲存格，再一次刪除整列，避免由上往下刪除時因列號位移造成的跳行問題。

Sub 刪除A欄空白列()
    On Error Resume Next: Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete: On Error GoTo 0
End Sub

此巨集以A欄作為判斷基準。若A欄有資料但其他欄為空，那列仍會保留，邏輯上等同於「A欄有資料才算有效列」。若A欄完全沒有空白儲存格，SpecialCells會報錯，也會由On Error Resume Next自動略過。

三、新增工作表並以日期命名

建立每日或每月紀錄工作表時，通常需要先新增工作表，再手動輸入日期名稱。這支巨集直接在最後一張工作表後新增新表，並以當天日期命名為yyyymmdd格式。例如2025年5月1日執行時，工作表名稱會自動設定為20250501。

Sub 新增日期工作表()
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = Format(Date, "yyyymmdd")
End Sub

如果同一天執行兩次，會因工作表名稱重複而出現錯誤。若需要防呆，可在新增前先檢查是否已有同名工作表再決定是否建立。

四、在第一張表建立工作表目錄

當活頁簿內的工作表越來越多時，要找到特定工作表往往需要不斷捲動標籤列。這支巨集會自動清空第一張工作表，建立「工作表目錄」標題，並為每張工作表建立超連結，點擊即可跳到該表的A1儲存格。

Sub 工作表目錄()
    Dim ws As Worksheet: With Sheets(1): .Cells.Clear: .[A1] = "工作表目錄": For Each ws In Worksheets: If ws.Name <> .Name Then .Hyperlinks.Add .Cells(.Cells(.Rows.Count, 1).End(xlUp).Row + 1, 1), "", "'" & ws.Name & "'!A1", , ws.Name: End If: Next: End With
End Sub

超連結的SubAddress格式為'SheetName'!A1，這是Excel內部超連結的標準寫法。每次執行都會重新建立整份目錄，因此新增或刪除工作表後只需再執行一次即可更新。

五、一鍵自動適配欄寬列高

貼入外部資料或調整儲存格內容後，常會出現欄寬不足或列高不合適的情況。這支巨集對整張工作表執行AutoFit，讓所有欄寬依內容自動調整，同時列高也會依文字長度與換行設定自動更新。

Sub 自動調整欄寬列高()
    Cells.EntireColumn.AutoFit: Cells.EntireRow.AutoFit
End Sub

AutoFit是依儲存格內容的顯示寬度計算。如果工作表中有合併儲存格，欄寬可能無法正確計算，因此建議先取消合併後再執行。

六、一鍵去除全表合併儲存格

合併儲存格雖然在版面呈現上方便，但會影響排序、篩選與樞紐分析。整理資料結構時，全面取消合併往往是必要的前置作業。這支巨集會對整張工作表執行Cells.UnMerge，一次解除所有合併。

Sub 取消合併()
    Cells.UnMerge
End Sub

取消合併後，原本的內容會保留在左上角儲存格，其餘儲存格恢復為空白。若需要將值填滿原本合併區域，可再搭配選取空白儲存格並填入上一列資料的方式完成。

七、以選取儲存格為準凍結窗格

凍結窗格是瀏覽大型報表時常用的功能。如果想調整凍結位置，通常需要先取消再重新設定。這支巨集把兩個動作合併為一個步驟，先解除既有凍結，再立即依目前選取的儲存格重新設定凍結分界。

Sub 凍結窗格()
    ActiveWindow.FreezePanes = False: ActiveWindow.FreezePanes = True
End Sub

執行前先選取想作為凍結分界右下角的儲存格。例如選取B2時，第一列與A欄會同時凍結。若不先解除凍結直接設定，原本的分界點不會更新。

八、匯出目前工作表為PDF至桌面

將報表轉成PDF分享給沒有Excel的同事，是辦公室常見需求。這支巨集透過WScript.Shell取得使用者桌面路徑，再以工作表名稱作為檔名，直接將目前工作表匯出為PDF存放到桌面。

Sub 匯出PDF到桌面()
    ActiveSheet.ExportAsFixedFormat xlTypePDF, CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\" & ActiveSheet.Name & ".pdf"
End Sub

SpecialFolders("Desktop")可以自動取得不同使用者的桌面路徑，因此不需要手動設定路徑。不過這個方法僅適用於Windows環境；在Mac系統中需改用其他方式取得桌面位置。

心得

這八支巨集看起來都很短，有些甚至只有一行，但每一支都對應到日常工作中的實際需求。像是清空資料保留公式、刪除空白列或凍結窗格，都是在處理Excel資料時經常遇到的情境。

如果每次都依賴手動操作，時間會在細碎動作中不斷流失。將這些巨集集中放在個人增益集或常用活頁簿中，搭配快速鍵或功能區按鈕，需要時一鍵執行，才是真正發揮VBA提高效率的價值。

同時，程式碼保持精簡也有好處。閱讀容易、理解快速，日後若需要修改或擴充功能，也不必花時間重新研究整段程式，這正是工具型巨集最理想的設計方式。

延伸閱讀

贊贊小屋相關部落格文章：

載入中...

贊贊小屋VBA教學中心：

Excel巨集執行、Excel巨集程式、Excel巨集程式碼、Excel VBA教學、VBA教學、Excel巨集範例、VBA UserForm、VBA VLOOKUP。

VBA課程推薦：零基礎入門進階的20小時完整內容

發佈日期：2026-03-05