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,

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,

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