How to define Column to sort with VBA

[ThrottleWorks]

Hi,

I am trying to sort a data.

I am finding header "reference" in the sheet and sort the data with base as "reference".

My problem is once I am finding 'reference' in the data, macro takes Column "U" as static.
The header 'reference' in U Column but my problem is 'reference' can be in Column T or R also.

Howe do I make my sorting Column dynamic.

I want to change ":=Range("U2:U65000")," to a dynamic range.
Can anyone please help me this.

Kindly note I have recorded below mentioned macro for my use.

Collate.Select
    Range("a1").Select
    Selection.AutoFilter
    Cells.Find(What:="Reference", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, Search Direction:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveWorkbook.Worksheets("CollatedData").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("CollatedData").AutoFilter.Sort.SortFields.Add Key _
        :=Range("U2:U65000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("CollatedData").AutoFilter.Sort
        .Header = xlYes
        .Match Case = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[chirayu]

You'll have to modify code to incorporate your other post

http://chandoo.org/forum/threads/use-dynamic-column-for-vlookup-in-vba.24903/

Basically after the Cells.Find().Activate bit store the column number
& then in the Autofilter criteria change Range("U2:U65000")
to Range(MyCol & "2:" & MyCol & "65000")

 

[ThrottleWorks]

Hi @chirayu , thanks a lot for the help.

Will share the results shortly. Have a nice day ahead.