Sorting multiple range using a macro

[onlinepredator]

Hi,

I am tyring to use a macro to sort a group of ranges C120, C2140,
......C31400. i need to sort each of these ranges based on entries in
coulmn C. There are 20 ranges here. Heres the code that I use to sort
the first two ranges.....

Range("C120").Select
Selection.Sort Key1:=Range("C1"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("C2140").Select
Selection.Sort Key1:=Range("C21"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

but i would prefer to use a for loop so that in case i change the
spreadsheet later it would be easy for me to change the code
too........

Dhanush

 

[kiza]

Dhanush,

I have looked at your coding and am a little unsure how you are sorting
your ranges as they seem to over lap. I have not changed your coding,
just added to it in order to provide you with the loop function.

Sub Sort()
'
Dim rwIndex, ColIndex
'
rwIndex = 1
ColIndex = 3
'
Do Until Range("C" & rwIndex) = ""
rwIndex = rwIndex + 1
Loop
Range(Cells(1, 3), Cells(rwIndex, ColIndex)).Select
'
Range("C120").Select
Selection.Sort Key1:=Range("C1"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("C2140").Select
Selection.Sort Key1:=Range("C21"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
'
Range("A1").Select
End Sub

It starts from C1 and continues looping until there is an empty cell in
Column C. Hence ColIndex being equal to 3.

Hope that this helps.
Kiza

 

[Don Guillett]

Let us know if this works by putting your ranges in a list
c1:d21
c22:d400
etc

Sub sortfromloop()
For Each c In Selection
'MsgBox c
x = Left(c, InStr(c, ":") - 1)
'MsgBox x
'Range("C120").Sort Key1:=Range("C1"), Order1:=xlAscending
Range(c).Sort Key1:=Range(x), Order1:=xlAscending
Next c
End Sub

Sub Macro5()
myname = ActiveCell
myref = ActiveCell.Offset(, 1).Address
Names.Add Name:=myname, RefersTo:=myref
End Sub

 

[onlinepredator]

Hi Kiza,

I already have lots of space in each of the ranges. I mean not al
range have contents in all their cells hence I am not sure as how t
change your code suggestion to suit my needs...... the problem is tha
i am not all that good with VB..... Thanks for yo
suggestions.

Hi Don Guillett,

I tried to copy your code into the macro and i am getting an erro
statement "Can't execute code in break mode"

heres the code

Range("A651:A670").Select
For Each c In Selection
'MsgBox c
x = Left(c, InStr(c, ":") - 1)
'MsgBox x
'Range("C120").Sort Key1:=Range("C1"), Order1:=xlAscending
Range(c).Sort Key1:=Range(x), Order1:=xlAscending
Next c

A651:A670 is where i have stored all the ranges that need to b
sorted... that is A651 = C120 -- A652 = C2140 etc

For now i am individualy sorting each range in the code......But woul
like to make the code short my looping

Dhanus

 

[Don Guillett]

On the toolbar just touch your reset button (blue square on mine) or
Run>reset
then
Sub sortfromloop()
For Each c In Range("A651:A670")
'MsgBox c
x = Left(c, InStr(c, ":") - 1)
'MsgBox x
'Range("C120").Sort Key1:=Range("C1"), Order1:=xlAscending
Range(c).Sort Key1:=Range(x), Order1:=xlAscending
Next c
End Sub