Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Naming variable range selected by cursor movement
ActiveWorkbook.Names.Add Name:="FORMAT_ALL", RefersTo:=Selection
Cliff Edwards |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can I limit cursor movement within specified range in excel? | Excel Discussion (Misc queries) | |||
excel limit data entry range, control cursor movement to 2 cols | Excel Programming | |||
Cursor Movement vs. Range Reference | Excel Programming | |||
how to confine the range of movement of cursor | Excel Programming | |||
how to confine the range of movement of cursor | Excel Programming |