Selection.AutoFill to work with dynamic range ?

Hi,

As i am a noob in excel, i require help. thanks in advance

I have attached a spread sheet in which i want it to populate a formula all the way down.

[ CODE ]'Selection.AutoFill Destination:=Range("C6:C15")'[ CODE ]

Now the problem is that my range will never be the same, as number of people who will get the letter will change on daily basis.

I.e if you look at example2 sheet & Example3.

I want the macro to look at columA & check total number of cases & then determine the range. i.e

[ CODE ]Selection.AutoFill Destination:=Range("C6:.End(xlDown).Row")[CODE ]

Sorry in advance if i failed to explain my question. Thanks again