如何移除Excel工作表A1:T16单元格范围外的网格线？

Alright, I get your problem—you want to keep gridlines only in the A1:T16 range and hide them everywhere else, but the global DisplayGridlines setting is all-or-nothing. Let's fix this with a clever workaround using cell borders to simulate gridlines for your target range.

Here's how to do it step-by-step:

1. Turn off global gridlines: First, we'll disable the worksheet's default gridlines so only our custom "gridlines" (borders) show up.
2. Clear existing borders: This ensures no old borders interfere with our new gridline simulation.
3. Add borders to your target range: We'll apply thin, light-gray borders (matching Excel's default gridline style) to every cell in A1:T16.

Full VBA Code #

Sub KeepGridlinesOnlyInA1T16()
    Dim targetWs As Worksheet
    ' Replace "YourSheetName" with the actual name of your worksheet
    Set targetWs = ThisWorkbook.Worksheets("YourSheetName")
    
    ' 1. Disable global gridlines for the workbook window
    targetWs.Parent.Windows(targetWs.Parent.Name).DisplayGridlines = False
    
    ' 2. Clear all existing borders from the entire worksheet
    targetWs.Cells.Borders.LineStyle = xlNone
    
    ' 3. Apply simulated gridlines to A1:T16
    With targetWs.Range("A1:T16").Borders
        .LineStyle = xlContinuous ' Solid line, same as default gridlines
        .Weight = xlThin          ' Thin weight, matches default gridline thickness
        .ColorIndex = xlAutomatic ' Uses Excel's default light-gray gridline color
    End With
End Sub

Customization Tips #

• Adjust border color: If you want to match a specific gridline color, replace .ColorIndex = xlAutomatic with .Color = RGB(217, 217, 217) (this is Excel's standard light-gray gridline color).
• Preserve existing borders: If your worksheet has other borders you don't want to erase, skip step 2 and instead clear borders only from the areas outside A1:T16. Here's how to modify that part:

  ' Clear borders outside A1:T16 instead of the whole sheet
  Dim clearRange As Range
  Set clearRange = targetWs.Range("A1:XFD1048576")
  Set clearRange = Application.Intersect(clearRange, targetWs.Range("A1:T16").EntireRow.Offset(16)) _
      .Union(Application.Intersect(clearRange, targetWs.Range("A1:T16").EntireColumn.Offset(, 20)))
  clearRange.Borders.LineStyle = xlNone
  

Notes #

• This method works because Excel's native gridlines are a sheet-level setting, but cell borders let us control "gridlines" at the cell range level.
• The simulated gridlines will look identical to Excel's default gridlines to the naked eye.

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