Naming variable range selected by cursor movement
 

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.SortFie lds.Clear
ActiveWorkbook.Worksheets("ESTIMATE").Sort.SortFie lds.Add Key:=Range( _
"SortKey"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("ESTIMATE").Sort.SortFie lds.Add Key:=Range( _
"Class"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("ESTIMATE").Sort.SortFie lds.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!

Naming variable range selected by cursor movement
 

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

Cliff Edwards

Naming variable range selected by cursor movement
 

Selection.Name = "myRange"

--
---
HTH

Bob


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



"Merritt Sakata" wrote in message
...
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.SortFie lds.Clear
ActiveWorkbook.Worksheets("ESTIMATE").Sort.SortFie lds.Add Key:=Range( _
"SortKey"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("ESTIMATE").Sort.SortFie lds.Add Key:=Range( _
"Class"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=
_
xlSortNormal
ActiveWorkbook.Worksheets("ESTIMATE").Sort.SortFie lds.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!