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

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

Yeah, I did something very similar, which gave me a declared variable
MyLastPopulatedCell (exactly like your "endrow") but I am having trouble
defining the range with this in part of the code:

'Define Name
Set MyNamedRange = Range(MyToStartCell & ":" & MyLastPopulatedCell)

'Range(MyToStartCell & ":" & MyToEndCell)

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

The commented out statement was the former code which gives the entire
column. I am frustrated because to me it is exactly the same code but the
new code just doesn't work.

I thought it may be because the variable wasn't defined or declared
correctly, but it is declared correctly. If I hover the mouse over the
variable name it shows the correct value. So Argggggg.... I am not sure
what I am doing incorrectly.



"windsurferLA" wrote:

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