Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CurrentRegion in 2007 Sort
I used to add currentregion to a sort macro in 2003 so that as the list
increased, the macro would automatically include the new items. I can't figure out how to do that in 2007. Below is a sample recorded macro with a specified range. How do I make it "grow" automatically as the list grows? Thanks. Sub Macro2() ActiveWorkbook.Worksheets("Quarter 1").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Quarter 1").Sort.SortFields.Add Key:=Range("E4"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Quarter 1").Sort .SetRange Range("A4:G78") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
CurrentRegion in 2007 Sort
Untested...
I'd qualify all the ranges: Sub Macro2() with ActiveWorkbook.Worksheets("Quarter 1") .Sort.SortFields.Clear .Sort.SortFields.Add _ Key:=.Range("E4"), _ SortOn:=xlSortOnValues, _ Order:=xlAscending, _ DataOption:=xlSortNormal with .sort .SetRange .Range("A4").currentregion .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With end with End Sub But there's no reason you can't use the old xl2003 syntax: with ActiveWorkbook.Worksheets("Quarter 1") with .range("a4").currentregion .sort key1:=.columns(5), _ order1:=xlAscending, _ header:=xlYes, _ OrderCustom:=1, _ MatchCase:=False, _ Orientation:=xlTopToBottom end with end with Andy wrote: I used to add currentregion to a sort macro in 2003 so that as the list increased, the macro would automatically include the new items. I can't figure out how to do that in 2007. Below is a sample recorded macro with a specified range. How do I make it "grow" automatically as the list grows? Thanks. Sub Macro2() ActiveWorkbook.Worksheets("Quarter 1").Sort.SortFields.Clear ActiveWorkbook.Worksheets("Quarter 1").Sort.SortFields.Add Key:=Range("E4"), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("Quarter 1").Sort .SetRange Range("A4:G78") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why can't I sort a 2003 file in 2007? 2007 freezes. | Excel Discussion (Misc queries) | |||
sort function for dates does not sort properly in Office 2007 Exc. | Excel Worksheet Functions | |||
currentregion | Excel Discussion (Misc queries) | |||
CurrentRegion less one Row | Excel Programming | |||
CurrentRegion | Excel Programming |