I am writing a For loop inside which I am filtering, sorting and then naming
a range. I am currently naming the entire column because I get compiling
errors everytime I try to select the last non empty cell in the column. Does
anyone know a more elegant way to do this?


For MyFromColumn = 1 To 11
MyToColumn = MyFromColumn + 26
MyFromStartCell = Cells(1, MyFromColumn).Address
MyFromEndCell = Cells(65536, MyFromColumn).Address
MyToStartCell = Cells(1, MyFromColumn + 26).Address
MyToEndCell = Cells(65536, MyFromColumn + 26).Address

'AutoFilter
Range(MyFromStartCell & ":" & MyFromEndCell).AdvancedFilter
Action:=xlFilterCopy, CopyToRange:=Range(MyToStartCell), Unique:=True
'Sort
Range(MyToStartCell & ":" & MyToEndCell).Select
Selection.Sort Key1:=Cells(2, MyToColumn), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'Define Name

'Set MyLastPopulatedCell = Cells(1, xlDown)

Set MyNamedRange = Range(MyToStartCell & ":" & MyToEndCell)

''Range(MyToStartCell & ":" & Selection.End(xlDown)).Select

ActiveWorkbook.Names.Add Name:=MyRangeName(MyFromColumn),
RefersToR1C1:=MyNamedRange

Next