Sorting range with areas

[Guest]

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,

 

[Bob Flanagan]

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

 

[Dave Peterson]

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.