如何在Excel排序后保留数据验证规则？

Hey there, let's sort out this frustrating issue where your data validation rules stay stuck in their original cells after sorting!

Why This Happens #

Excel’s data validation rules are tied to cell positions, not the content inside the cells. When you run a sort, only the cell contents move—your validation rules stay attached to the original cell locations, which is why they don’t follow your sorted data.

Modified VBA Code to Preserve Validation Rules #

Here’s an updated version of your Sort_Data macro that saves your validation rules before sorting, then reapplies them to the correct rows after sorting:

Sub Sort_Data()
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim i As Long, j As Long
    Dim dvRules As Variant ' Stores validation rules for each row/column
    Dim targetCols As Variant ' Columns with custom validation (L=12, N-S=14-19, U-AC=21-29)
    Dim col As Variant
    Dim currentRow As Long
    
    ' Define columns that have data validation rules
    targetCols = Array(12, 14, 15, 16, 17, 18, 19, 21, 22, 23, 24, 25, 26, 27, 28, 29)
    
    For Each ws In Sheets(Array("North", "Central", "Onshore", "South"))
        ws.AutoFilter.ShowAllData
        LastRow = ws.Cells(Rows.Count, 2).End(xlUp).Row
        
        ' Step 1: Save all validation rules for target columns
        ReDim dvRules(3 To LastRow, 1 To UBound(targetCols) + 1)
        For currentRow = 3 To LastRow
            For j = 0 To UBound(targetCols)
                col = targetCols(j)
                With ws.Cells(currentRow, col)
                    If .Validation.Type <> xlValidateNone Then
                        ' Save core validation properties
                        dvRules(currentRow, j + 1) = Array( _
                            .Validation.Type, _
                            .Validation.AlertStyle, _
                            .Validation.Formula1, _
                            .Validation.Formula2 _
                        )
                    Else
                        dvRules(currentRow, j + 1) = Empty
                    End If
                End With
            Next j
        Next currentRow
        
        ' Step 2: Run your original sort logic
        ws.Sort.SortFields.Clear
        ws.Sort.SortFields.Add Key:=ws.Range("F3:F" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        ws.Sort.SortFields.Add Key:=ws.Range("B3:B" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        ws.Sort.SetRange ws.Range("A3:AE" & LastRow)
        ws.Sort.Header = xlNo ' Explicitly set since we're sorting data only (no headers in range)
        ws.Sort.Apply
        
        ' Step 3: Reapply saved validation rules to sorted rows
        For currentRow = 3 To LastRow
            For j = 0 To UBound(targetCols)
                col = targetCols(j)
                With ws.Cells(currentRow, col)
                    ' Clear existing validation first
                    If .Validation.Type <> xlValidateNone Then
                        .Validation.Delete
                    End If
                    ' Reapply the saved rule if it existed
                    If Not IsEmpty(dvRules(currentRow, j + 1)) Then
                        .Validation.Add _
                            Type:=dvRules(currentRow, j + 1)(0), _
                            AlertStyle:=dvRules(currentRow, j + 1)(1), _
                            Formula1:=dvRules(currentRow, j + 1)(2), _
                            Formula2:=dvRules(currentRow, j + 1)(3)
                        ' Keep dropdowns enabled if your rules used them
                        .Validation.InCellDropdown = True
                    End If
                End With
            Next j
        Next currentRow
    Next ws
End Sub

Key Details About the Code #

• Target Columns: The targetCols array lists the column numbers for L, N:S, and U:AC (Excel uses 1-based numbering, so L=12, AC=29).
• Saving Rules: We store critical validation properties (type, alert style, formulas) in an array before sorting.
• Reapplying Rules: After sorting, we clear any existing validation in the target cells and reattach the saved rules to the new row positions.

Bonus: Customize for Additional Validation Properties #

If your rules include custom input prompts or error messages, add these properties to the saved array and reapply them:

' When saving rules
dvRules(currentRow, j + 1) = Array( _
    .Validation.Type, _
    .Validation.AlertStyle, _
    .Validation.Formula1, _
    .Validation.Formula2, _
    .Validation.InputTitle, _
    .Validation.InputMessage, _
    .Validation.ErrorTitle, _
    .Validation.ErrorMessage _
)

' When reapplying rules
.Validation.InputTitle = dvRules(currentRow, j + 1)(4)
.Validation.InputMessage = dvRules(currentRow, j + 1)(5)
.Validation.ErrorTitle = dvRules(currentRow, j + 1)(6)
.Validation.ErrorMessage = dvRules(currentRow, j + 1)(7)

Preventative Tip for Future Setup #

If you haven’t already set up all your validation rules, apply them to entire data ranges (e.g., L3:L[LastRow]) instead of individual cells. Range-based validation automatically applies to all cells in the range, even after sorting, since the rule is tied to the range rather than specific cell positions.

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