Naming variable range selected by cursor movement

[Merritt Sakata]

following code via macro recorder is intended to create a named database
range "Format_all" via cursor movement selection and then to format for
printing uisng data sort commands. But I need to eliminate fixed range
definition since the number of rows of data needs to be able to change freely
with the cursor selection.

Can you help?


Sub Format_All()
'
' Format_All Macro
' Format All Records for Printing
'

'
Application.Goto Reference:="Format_Start"
Selection.End(xlDown).Select
Selection.End(xlToLeft).Select
Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Names.Add Name:="FORMAT_ALL", RefersToR1C1:="r9c1:R936C43"
ActiveWorkbook.Names("Format_all").Comment = ""
ActiveWorkbook.Worksheets("ESTIMATE").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("ESTIMATE").Sort.SortFields.Add Key:=Range( _
"SortKey"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("ESTIMATE").Sort.SortFields.Add Key:=Range( _
"Class"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("ESTIMATE").Sort.SortFields.Add Key:=Range( _
"Description"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("ESTIMATE").Sort
.SetRange Range("Format_all")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub


thanks!

 

[ward376]

ActiveWorkbook.Names.Add Name:="FORMAT_ALL", RefersTo:=Selection

Cliff Edwards

 

[Bob Phillips]

Selection.Name = "myRange"

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)