Naming variable range selected by cursor movement

M

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!
 
B

Bob Phillips

Selection.Name = "myRange"

--
---
HTH

Bob


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Modify Sort Routine to inlcude All Data 2
sort problem 1
Undo Macro Action 3
Sort Dynamic Range 4
vba dynamic 1
Sorting Question 5
Clear Check Box 2
sort table listobject without table name 5

Top