CODEWITHSUNDEEP
excelvba
Leedo
Leedo

Reputation: 611

How to preserve these Data validation rules after sorting?

I set Data Validation rules on multiple columns (L,N:S,U:AC) and these rules not the same in all columns,meaning some columns are Numeric or Yes,No and fixed strings.
Note: first two Rows are header.

Problem: When sorting a range in Excel, the cell values are moved, but Data validation rules not moved.

kindly, How to preserve these Data validation rules after sorting ?

as always, your support is highly appreciated. I am using below code for sort

Sub Sort_Data()
    Dim ws As Worksheet
    Dim SourceRng As Range
    Dim LastRow As Long
    
  For Each ws In Sheets(Array("North", "Central", "Onshore", "South"))

    ws.AutoFilter.ShowAllData
    ws.Sort.SortFields.Clear
    
    LastRow = ws.Cells(Rows.Count, 2).End(xlUp).Row
                                                                                 
    ws.Sort.SortFields.Add Key:=Range("F3:F" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ws.Sort.SortFields.Add Key:=Range("B3:B" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ws.Sort.SetRange Range("A3:AE" & LastRow)
    ws.Sort.Apply
   Next ws
End Sub

Upvotes: 0

Views: 796

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57693

This is not possible with built in sorting in Excel.

Sorting only moves the values (data) inside the cells but does not move the cells itself. Therefore no data validation rules are moved.

A workaround can be to let your macro apply the rules to the cells again after sorting.

Upvotes: 1

Related Questions