带单元格输入限制的表格排序与筛选问题咨询

一、无VBA实现方案（推荐） #

直接用Excel的**结构化表格（List Object）**替代普通数据区域，它会自动维护数据验证的有效性，排序、筛选后不会出现输入异常：

• 选中包含表头的数据区域
• 点击菜单栏「插入」→「表格」，勾选「我的表格有标题」后确认
• 选中需要设置下拉验证的列（比如L列），点击「数据」→「数据验证」：
  • 验证类型选「序列」
  • 来源输入i.O,n.i.O.
  • 按需勾选「忽略空值」「在单元格中显示下拉箭头」
• 完成后，无论排序、筛选还是新增行，该列的下拉验证都能正常生效，无需额外操作。

二、VBA方案：筛选时重新调用验证函数 #

如果必须保留普通数据区域，可以通过工作表事件在筛选操作触发时重新应用数据验证：

1. 封装验证函数 #

把验证逻辑封装成可调用的子过程：

Sub ApplyValidation()
    Dim LR As Long
    LR = Range("A" & Rows.Count).End(xlUp).Row
    ' 对L列可见单元格应用下拉验证
    With Range("L9:L" & LR).SpecialCells(xlCellTypeVisible).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
             Operator:=xlBetween, Formula1:="i.O,n.i.O."
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
End Sub

2. 添加工作表触发事件 #

右键点击工作表标签→「查看代码」，粘贴以下事件代码：

Private Sub Worksheet_Calculate()
    ' 筛选后表格触发计算时，重新应用验证
    If Me.AutoFilterMode Then
        ApplyValidation
    End If
End Sub

Private Sub Worksheet_Activate()
    ' 切换回工作表时，重新验证确保正常
    If Me.AutoFilterMode Then
        ApplyValidation
    End If
End Sub

说明：Excel筛选操作不会直接触发Worksheet_Change事件，因此用Worksheet_Calculate捕捉筛选后的计算动作；Worksheet_Activate用于避免切换工作表后出现验证异常。

3. 测试验证 #

设置完成后，尝试对表头进行筛选或排序，验证函数会自动执行，L列的下拉验证即可恢复正常输入。

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