Excel VBA alternative way of sorting by date

Question

Hi, I'm currently working on a document which has about 25 columns, each headed by a date. Now I have to sort these columns by date. However, the sorting should not start with January and run until December. In my document, the sorting should start with September, and run until June (July and August can be omitted, as my document relates to a schoolyear, which runs from September to June in Belgium).

Is there anyone who can help me with the code for this kind of sorting? I first select the required range, and then use Selection.Sort, but then it always sorts starting with January.

Many thanks in advance!

Kind regards, Marc

Answer 1

Say, you have the month number from 1 to 12 in row 1. Add a helper row, containing the formula

=MOD(A1-8+12,12), =MOD(B1-8+12,12), ...

(add the formula in the first cell and drag it to the right; "8" is for "September")

Sort the columns by this value.

Answer 2

That is definitely useful, but it is the end user who determines which month/date will be entered. I have to provide a means to sort the columns starting in september, without having the exact data beforehand.

Is there a way to do this in VBA?

Thanks anyway for the idea of a helper row, this concept was new to me, but it might be useful in the future.

Kind regards, Marc