錄製巨集怎麼改成動態 VBA？自動美化表格與恢復原狀完整實作

重點摘要

- 錄製器產生的固定最後一列，需要改成由資料動態計算的 lastRow。 - 正式 VBA 應限定活頁簿與工作表，避免依賴 Selection、ActiveCell。 - 美化程序可整合公式、交錯列、標題與框線；還原程序則分別清除格式及公式。 - 含 VBA 的檔案必須儲存為 .xlsm 或其他支援巨集的格式。 錄製巨集後最值得學的修改，是把固定範圍改成動態範圍。本文將錄製結果整理成兩個程序：「美化表格」會依 A 欄最後一筆資料填入總薪資並套用格式；「恢復原狀」則清除格式及 H 欄公式，方便反覆練習與測試。

為什麼固定範圍會讓巨集失效？

錄製時若資料到第 1,443 列，程式就可能出現 Range("H2:H1443")。資料增加時，新列不會被處理；資料減少時，又會把公式與格式延伸到空白區。解法是每次執行都重新取得最後一列。

動態最後一列應該怎麼取得？

從工作表最底部的 A 欄向上尋找第一個非空白儲存格，是較常見且比 End(xlDown) 穩定的寫法： ``vba lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row | 程式片段 | 說明 | |---|---| | ws.Rows.Count | 工作表可用的最後一列編號 | | Cells(..., "A") | A 欄最底部的儲存格 | | .End(xlUp) | 從底部往上找到第一個有內容的儲存格 | | .Row | 取得該儲存格的列號 | | lastRow As Long` | 用 Long 整數變數保存最後一列 |

如何建立動態美化表格程序？

前置條件

• 第一列為標題，A 欄每筆資料都有員工代碼。
• E:G 是可加總的數值欄，H 欄為總薪資。
• 工作表使用虛擬名稱「薪資資料」；請依自己的練習檔修改。
• 執行前保留備份，並只啟用來源可信的巨集。

① 🧱 插入新程序與宣告物件

在 Visual Basic 編輯器選取「插入」→「程序」，建立 美化表格。先用 Worksheet 變數指向明確工作表，避免作用到目前碰巧被選取的工作表。

② 🔎 計算最後一列

以 A 欄作為判斷欄，取得 lastRow。若只有標題列就離開程序，避免 AutoFill 的目的範圍不成立。

③ 🧮 在 H2 寫入相對公式

使用 FormulaR1C1 寫入 =SUM(RC[-3]:RC[-1])，表示把同一列左側三欄到左側一欄加總。

④ 📌 將公式填到動態終點

把錄製結果的固定 H1443 改為 "H2:H" & lastRow。當最後一列是 2000，字串會組成 H2:H2000。

⑤ 🎨 套用交錯列、標題與框線

先清除舊條件式格式，避免重複執行後規則越疊越多；再建立奇數列與偶數列規則，最後設定標題及框線。

⑥ ✅ 執行後核對結果

檢查 H2 與最後一列都有公式、交錯列顏色正確、標題格式沒有被奇數列覆蓋，並確認資料範圍外沒有多餘格式。

完整 VBA 範例

Option Explicit

Sub 美化表格()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dataRange As Range
    Dim formulaRange As Range

    Set ws = ThisWorkbook.Worksheets("薪資資料")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < 2 Then
        MsgBox "目前沒有可處理的資料。", vbInformation
        Exit Sub
    End If

    Set dataRange = ws.Range("A1:H" & lastRow)
    Set formulaRange = ws.Range("H2:H" & lastRow)

    '填入並向下複製總薪資公式
    ws.Range("H2").FormulaR1C1 = "=SUM(RC[-3]:RC[-1])"
    If lastRow > 2 Then
        ws.Range("H2").AutoFill Destination:=formulaRange
    End If

    '避免重複執行時累積條件式格式規則
    dataRange.FormatConditions.Delete

    '奇數列：淺黃色
    With dataRange.FormatConditions.Add( _
        Type:=xlExpression, Formula1:="=MOD(ROW(),2)=1")
        .Interior.Color = RGB(255, 242, 204)
    End With

    '偶數列：淺橘色
    With dataRange.FormatConditions.Add( _
        Type:=xlExpression, Formula1:="=MOD(ROW(),2)=0")
        .Interior.Color = RGB(252, 228, 214)
    End With

    '標題列使用直接格式，確保清楚顯示
    With ws.Range("A1:H1")
        .Interior.Color = RGB(189, 215, 238)
        .Font.Bold = True
    End With

    '加入所有框線
    With dataRange.Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With

    Application.Goto ws.Range("A1"), True
    MsgBox "表格美化完成。", vbInformation
End Sub

如何建立「恢復原狀」程序？

清除格式與清除內容是不同動作。要保留 A:G 的資料，不能對整張表執行 Clear；應對資料範圍執行 ClearFormats，再只對 H2:H最後一列執行 ClearContents。

① 🔎 重新取得最後一列

不要假設還原時的筆數與美化時相同。每次執行都重新計算 lastRow。

② 🧹 清除資料範圍格式

ClearFormats 會清除一般格式；條件式格式可能仍需用 FormatConditions.Delete 明確移除。

③ 🗑️ 只清除 H 欄公式

ClearContents 會移除公式與值，但保留格式。範圍只限定 H2:H最後一列，避免刪除來源資料。

④ ✅ 回到 A1 並驗證

完成後選取 A1，檢查 A:G 資料仍在、H 欄公式已清除、交錯列與框線已移除。 ``vba Sub 恢復原狀() Dim ws As Worksheet Dim lastRow As Long Dim dataRange As Range Set ws = ThisWorkbook.Worksheets("薪資資料") lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row If lastRow < 1 Then Exit Sub Set dataRange = ws.Range("A1:H" & lastRow) dataRange.FormatConditions.Delete dataRange.ClearFormats If lastRow >= 2 Then ws.Range("H2:H" & lastRow).ClearContents End If Application.Goto ws.Range("A1"), True MsgBox "已恢復原始資料狀態。", vbInformation End Sub

如何測試程式確實支援不同資料筆數？

① 🧪 先用 10 列虛擬資料測試

執行「美化表格」，確認公式、色彩及框線；再執行「恢復原狀」，確認來源資料沒有被刪除。

② ➕ 新增數列資料再測試

在 A:G 新增幾列虛擬資料，重新執行巨集，確認 H 欄與格式能延伸到新的最後一列。

③ ➖ 刪除部分資料再測試

刪除底部幾列資料後重跑，確認程式不會繼續處理舊的固定終點。

④ ▶️ 使用 F8 逐行執行

在 Visual Basic 編輯器按 F8，觀察 lastRow、範圍設定及每段格式處理。若出錯，可更快定位是哪一行。

專有名詞說明

給 AI 的 VBA 修改提示詞

你是一位資深 Excel VBA 程式碼審查員。請將下列錄製巨集改成容易維護的版本。

需求：
1. 使用 ThisWorkbook.Worksheets("薪資資料") 明確限定工作表；
2. 以 A 欄取得動態最後一列，使用 Long 變數 lastRow；
3. 將所有固定的 H2:H1443 改成 H2:H & lastRow；
4. 儘量移除 Select、Selection 與 ActiveCell；
5. 重複執行時不可累積條件式格式；
6. 提供「美化表格」與「恢復原狀」兩個完整程序；
7. 加入繁體中文註解、無資料判斷及測試清單；
8. 不得刪除 A:G 的原始資料；
9. 範例不得包含真實公司名稱、路徑、姓名或薪資。

[貼上已去除機敏資訊的錄製巨集]

常見錯誤與排除方式

• 下標超出範圍：工作表名稱與程式中的「薪資資料」不一致。
• AutoFill 方法失敗：目的範圍只有一格或 H2 沒有正確公式；程式已用 lastRow > 2 避免部分狀況。
• 重跑後規則愈來愈多：建立新規則前先執行 FormatConditions.Delete。
• 還原後原始資料消失：誤用整個資料範圍的 Clear；應分別使用 ClearFormats 與 H 欄的 ClearContents。
• 重新開檔後巨集不見：檔案被存成 .xlsx，應另存為 .xlsm。

常見問題

可以沿用逐字稿中的變數 x 嗎？

可以，但 lastRow 更能表達用途。清楚的變數名稱能降低維護成本，也方便 AI 或同事理解程式邏輯。

為什麼範例沒有使用 Selection.Rows.Count？

先選取範圍再計數會依賴畫面狀態；從指定工作表底部向上找最後一列，通常更明確，也較不受中間選取動作影響。

清除格式和全部清除有什麼差別？

ClearFormats 只移除格式；ClearContents 只移除內容；Clear 會同時清除內容、格式等。要保護來源資料時必須謹慎選擇。

巨集完成後要儲存成哪一種格式？

選取「檔案」→「另存新檔」，把檔案類型改成「Excel 啟用巨集的活頁簿（*.xlsm）」。一般 .xlsx 不會保存 VBA 專案。

延伸閱讀

• Excel VBA 效率提升主題文章
• Excel 錄製巨集前的表格美化流程規劃
• 分段錄製 SUM、AutoFill 並讀懂 VBA 程式碼
• Excel 功能、函數、巨集與 VBA 如何選？

參考來源

- Microsoft Learn（繁體中文）：Excel Range 物件 - Microsoft 支援（繁體中文）：在 Excel 中執行巨集 - Microsoft 支援（繁體中文）：儲存巨集 - Microsoft 支援（繁體中文）：變更 Excel 中的巨集安全性設定 --- 作者：施文華 企業自動化顧問／講師，專注於 Excel、VBA、Power BI、Power Automate 與企業流程自動化。本文依課程逐字稿、實務操作與 Microsoft 官方文件整理。 作者介紹：關於施文華老師 最後更新：2026-09-08 適用版本／測試環境：Windows 版 Excel 2016 與 Microsoft 365。