求可自动灰化Excel未使用区域的VBA代码（无页码及分页符）

Absolutely! I totally get why those page numbers and breaks in Page Break View are such a nuisance—you just want that clean grayed-out unused area without the extra clutter. Let me share two solid solutions that do exactly what you need, no pagemarks in sight:

This macro identifies your worksheet's used range, then applies the same light gray shade from Page Break View to all unused cells. It doesn't trigger Page Break Mode, so no numbers or breaks will appear.

Sub GrayOutUnusedCells()
    Dim ws As Worksheet
    Dim usedRange As Range
    Dim lastRow As Long, lastCol As Long
    Dim unusedCells As Range
    
    ' Target the active worksheet (swap with Sheet1.Name if you want a specific sheet)
    Set ws = ActiveSheet
    
    ' Get the range of cells you've actually used
    Set usedRange = ws.UsedRange
    lastRow = usedRange.Row + usedRange.Rows.Count - 1
    lastCol = usedRange.Column + usedRange.Columns.Count - 1
    
    ' Define all unused cells: rows below the used range + columns right of it
    Set unusedCells = Union( _
        ws.Range(ws.Cells(lastRow + 1, 1), ws.Cells(ws.Rows.Count, ws.Columns.Count)), _
        ws.Range(ws.Cells(1, lastCol + 1), ws.Cells(lastRow, ws.Columns.Count)) _
    )
    
    ' Apply the default Page Break View gray (RGB 217,217,217)
    If Not unusedCells Is Nothing Then
        unusedCells.Interior.Color = RGB(217, 217, 217)
    End If
    
    ' Uncomment the line below if you need to reset the gray fill later
    ' usedRange.Interior.ColorIndex = xlColorIndexNone
End Sub

How to use: #

1. Press Alt + F11 to open the VBA Editor
2. Insert a new module (Right-click your workbook > Insert > Module)
3. Paste the code above
4. Run the macro (Press F5 or assign it to a button for quick access)

If you want the grayed area to automatically adjust when you add new content (no need to re-run the macro every time), this conditional formatting approach is perfect. It sets up rules that only gray cells outside your current used range.

Sub SetDynamicGrayFormatting()
    Dim ws As Worksheet
    Dim usedRange As Range
    Dim lastRow As Long, lastCol As Long
    
    Set ws = ActiveSheet
    Set usedRange = ws.UsedRange
    lastRow = usedRange.Row + usedRange.Rows.Count - 1
    lastCol = usedRange.Column + usedRange.Columns.Count - 1
    
    ' Clear any existing conditional formats first to avoid conflicts
    ws.Cells.FormatConditions.Delete
    
    ' Rule 1: Gray all rows below the used range
    With ws.Range(ws.Cells(lastRow + 1, 1), ws.Cells(ws.Rows.Count, ws.Columns.Count)).FormatConditions.Add(Type:=xlExpression, Formula1:="=TRUE")
        .Interior.Color = RGB(217, 217, 217)
        .StopIfTrue = False
    End With
    
    ' Rule 2: Gray all columns right of the used range
    With ws.Range(ws.Cells(1, lastCol + 1), ws.Cells(lastRow, ws.Columns.Count)).FormatConditions.Add(Type:=xlExpression, Formula1:="=TRUE")
        .Interior.Color = RGB(217, 217, 217)
        .StopIfTrue = False
    End With
End Sub

Bonus Tip: #

If you want this to update automatically whenever you edit the sheet, add this event code to your worksheet's code module (right-click the sheet tab > View Code):

Private Sub Worksheet_Change(ByVal Target As Range)
    SetDynamicGrayFormatting
End Sub

Just note that this will run the macro every time you make a change, so it’s best for smaller worksheets to avoid lag.

Important Notes: #

• Save your workbook as an .xlsm file (Macro-Enabled Workbook) to keep the VBA code
• Both solutions use the exact same gray color as Page Break View, so it’ll look identical minus the pagemarks
• To reset everything, either run the commented-out line in Solution 1 or delete the conditional formats manually

内容的提问来源于stack exchange，提问作者JohnKimble