Sort / subsort a range of cells based on the cell selected ??

[James 35]

Hi I'd like to automate excels inbuilt sort / subsort for a non static range of cells ...
The range is based on
5 columns including and adjacent to the selected cell and say 66 rows down ...
Ie . EV4:EZ70
The initial range being sorted alphabetically by cells EY4:EY70

Then a subsort numerically EW4:EW70

Only to sort cells in the range that have values in EV4:EV70...

When I record a macro I get this ??

Sub sortexample()

'

' sortexample Macro

'

 

'

    Range("EV4:EZ70").Select

    ActiveWorkbook.Worksheets("Period 12").Sort.SortFields.Clear

    ActiveWorkbook.Worksheets("Period 12").Sort.SortFields.Add Key:=Range( _

        "EY4:EY70"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _

        xlSortNormal

    ActiveWorkbook.Worksheets("Period 12").Sort.SortFields.Add Key:=Range( _

        "EW4:EW70"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _

        xlSortNormal

    With ActiveWorkbook.Worksheets("Period 12").Sort

        .SetRange Range("EV4:EZ70")

        .Header = xlGuess

        .MatchCase = False

        .Orientation = xlTopToBottom

        .SortMethod = xlPinYin

        .Apply

    End With

    ActiveWindow.SmallScroll Down:=-48

Any help is appreciated ! Thanks

 

[James 35]

Hmmm no takers,

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...ange-of-cells-based-on-the-cell-selected.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[James 35]

Apologies I have amended the original post and provided links to the other post

 

[James 35]

Actually scratch that I don't appear to be able to edit ???