Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting range with areas
Hi
I have a range which could be of different area selections. For example rng1 address could be $A$37:$D$38 or $A$25:$D$26,$A$43:$D$47 or $A$48:$D$49,$A$57:$D$57,$A$63:$D$63. Can somebody help me out to write a generic VBA macro to sort the range according to column 1? Help would really be appreciated. Thanks and regards, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting range with areas
VJ, the following illustrates how to do it with a selection:
With Selection .Sort Key1:=.Cells(1, 1), Order1:=xlAscending, _ Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End With You can change Selection to any range. Note sortkey1 has a period in front of Cells. This makes .Cells(1,1) refer to the top left cell as the With range (in this case the selection). If you wanted to sort on the second column instead, and your data has two or more columns, then it would be .Cells(1,2). You would need to set Header to xlYes or xlNo. Robert Flanagan Macro Systems Delaware, U.S. 302-234-9857 http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "VJ" wrote in message ... Hi I have a range which could be of different area selections. For example rng1 address could be $A$37:$D$38 or $A$25:$D$26,$A$43:$D$47 or $A$48:$D$49,$A$57:$D$57,$A$63:$D$63. Can somebody help me out to write a generic VBA macro to sort the range according to column 1? Help would really be appreciated. Thanks and regards, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting range with areas
Do you mean you want to sort each area separately?
if yes: dim myArea as range dim myRng as range set myrng = activesheet.range("$A$25:$D$26,$A$43:$D$47") for each myArea in myRng.areas with myarea .sort key1:=.cells(1), order1:=xlascending, header:=xlyes'?? end with next myarea Watch for typos, I composed in the message. VJ wrote: Bob, I gets the error when there are multiple areas such as $A$25:$D$26,$A$43:$D$47 but its works fine where are only one area such as $A$25:$D$26. I need some help in multiple areas. The error msg I get "The command you chose cannot be performed with multiple selection. Select a single range and click the command again" So I need to sort with multple areas. Thanks "Bob Flanagan" wrote: VJ, the following illustrates how to do it with a selection: With Selection .Sort Key1:=.Cells(1, 1), Order1:=xlAscending, _ Header:=xlYes, _ OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom End With You can change Selection to any range. Note sortkey1 has a period in front of Cells. This makes .Cells(1,1) refer to the top left cell as the With range (in this case the selection). If you wanted to sort on the second column instead, and your data has two or more columns, then it would be .Cells(1,2). You would need to set Header to xlYes or xlNo. Robert Flanagan Macro Systems Delaware, U.S. 302-234-9857 http://www.add-ins.com Productivity add-ins and downloadable books on VB macros for Excel "VJ" wrote in message ... Hi I have a range which could be of different area selections. For example rng1 address could be $A$37:$D$38 or $A$25:$D$26,$A$43:$D$47 or $A$48:$D$49,$A$57:$D$57,$A$63:$D$63. Can somebody help me out to write a generic VBA macro to sort the range according to column 1? Help would really be appreciated. Thanks and regards, -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting range | Excel Discussion (Misc queries) | |||
sorting range | Excel Discussion (Misc queries) | |||
Sorting by range | New Users to Excel | |||
Sorting a Range | Excel Discussion (Misc queries) | |||
sorting a range | Excel Discussion (Misc queries) |