Excel带空白单元格的动态优先级自动调整问题求助

问题场景 #

A列存在包含空白单元格的优先级列表，示例数据如下：

A1: Headline
A2: 3
A3: 1
A4: 空白
A5: 4
A6: 空白
A7: 2

当修改某单元格优先级（如将A5改为1）时，要求非空白单元格自动重新分配连续优先级（A2→4、A3→2、A7→3），但空白单元格需保持空白。原VBA代码会填充空白单元格，无法满足需求。

原代码问题分析 #

原代码遍历目标区域时，无论单元格原本是否空白，都会直接赋值iCount，导致空白单元格被填充，这是核心问题。

修改后的VBA代码 #

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myVal As Variant
    Dim iCount As Long
    Dim cell As Range
    Dim myRange As Range
    Dim nonBlankCells As Collection
    Dim currentCell As Variant
    
    Set myRange = Range("A2:A200")
    
    ' 仅处理单个目标区域内的单元格修改
    If Intersect(Target, myRange) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
    ' 修改空白单元格时直接退出，不触发优先级更新
    If Target.Value = "" Then
        Application.EnableEvents = True
        Exit Sub
    End If
    
    Application.EnableEvents = False
    
    myVal = Target.Value
    Set nonBlankCells = New Collection
    
    ' 收集所有非空白且非目标修改的单元格
    For Each cell In myRange
        If cell.Address <> Target.Address And cell.Value <> "" Then
            nonBlankCells.Add cell
        End If
    Next cell
    
    ' 对非空白单元格按原优先级升序排序
    Dim i As Integer, j As Integer
    For i = 1 To nonBlankCells.Count - 1
        For j = i + 1 To nonBlankCells.Count
            If nonBlankCells(i).Value > nonBlankCells(j).Value Then
                Dim temp As Range
                Set temp = nonBlankCells(i)
                Set nonBlankCells(i) = nonBlankCells(j)
                Set nonBlankCells(j) = temp
            End If
        Next j
    Next i
    
    ' 重新分配优先级，跳过用户设置的目标值
    iCount = 1
    For Each currentCell In nonBlankCells
        If iCount = myVal Then
            iCount = iCount + 1
        End If
        currentCell.Value = iCount
        iCount = iCount + 1
    Next currentCell
    
    Application.EnableEvents = True
End Sub

关键改动说明 #

1. 筛选非空白单元格：通过nonBlankCells集合仅收集需要更新的非空白单元格，完全跳过空白单元格，避免误填充
2. 排序非空白单元格：对收集到的单元格按原优先级升序排序，确保重新分配的优先级逻辑有序
3. 跳过目标值分配：在分配优先级时，跳过用户设置的目标值myVal，保证其他单元格的优先级连续且不重复
4. 空白修改判断：新增判断逻辑，当用户修改空白单元格时直接退出，无需触发优先级更新

测试验证 #

修改A5为1后，A2自动变为4、A3变为2、A7变为3，A4、A6保持空白，完全符合需求。

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