Excel VBA: sorting a table featuring cells with drop-down lists

Question

Let's say I have an Excel table featuring one column ("B" for example) in which every cell has a different drop-down list for validation. My application is such that different cells in "B" may contain different items in their corresponding drop-down lists. I want to sort my table using a different column as the key, for example column "A", which contains dates, in ascending order.

My problem is that Excel's Sort function (apparently) does not include validation lists. Therefore, after sorting, each cell in "B" will most likely end up with the wrong drop-down list. Can somebody suggest a simple way to implement the sorting in VBA such that it includes the drop-down lists? I know I could try sorting the table first and then populating "B" with the drop-downs, but I want to avoid that option if possible.

Answer 1

You are right, Sorting seems to not move the Validation Rules.

One work around would be to rewrite your validation rules to be the same for all cells. This can be achieved as follows:

• Arrange your validation lists in a block, headed with the Key values, eg using your sample data

       F    G    H    I    J
  1    1    2    3    4    5
  2    1a   2a   3a   4a   5a
  3    1b   2b   3b   4b   5b
  4    1c   2c   3c   4c   5c
  

• Then write the Validation rule as (List) (for row 2) =INDEX($F$2:$J$4,,MATCH($A2,$F$1:$J$1,0)) and copy down

This uses the value from column A as a lookup in row 1 to determine the block to use as the validation list.