Perhaps this will help. It finds the last active cell in column A and
sets the variable "endrow" equal to the number corresponding to the row
containing the last active cell in column A.

Sub LastRowFind()
Worksheets(1).Unprotect
ActiveSheet.Range("a16384").Select
Selection.End(xlUp).Select
endrow = ActiveCell.Row()
Worksheets("scratch").Range("C6").Value = endrow
Worksheets(1).Protect
End Sub

With knowledge of the last row, one can then limit range to that from
row 1 to row "endrow."

WindsurferLA


DynamiteSkippy wrote:
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