Sorting question

[Mr930]

I have 15 columns of numbers, they will always be the same length. I need to sort each one independent of the others. The columns are side by side all the time, and are calculated in an existing function. I would like to do this in VBA rather than doing an sort from the menu. I would like to create a function or subroutine that can take a passed in range and give back the sorted range. I am sure there are various ways to do this, can anyone suggest the best?

thanks
Fred

 

[mtb'r]

one option

Here is one possible solution. I'm not sure if it's "The Best" but it should work:

Sub Sort_Selection(Optional r As Range)
'Created by 4XL Solutions on 9/8/06
'Get the Address of the selected range
If r Is Nothing Then Set r = ActiveWindow.RangeSelection
'Get the first cell in the selected range
k = Left(r.Address, InStr(1, r.Address, ":") - 1)
' Sort the selected range
r.Sort Key1:=Range(k), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

This macro will accept a passed range, and sort that passed range. Also, it can be run directly by first selecting the range in Excel manually, then running the macro.

Good luck!

 

[Mr930]

Thanks!

Question, what does the word "optional" mean below? I have never seen that before.

Sub Sort_Selection(Optional r As Range)



Fred

 

[mtb'r]

"Optional" means that the macro can accept a passed variable, like you would do with a function, but it is not required. The fact that it is optional means that the macro will still run, even if a variable isn't passed.

In the latter case, the macro will sort the range that is currently selected in the active worksheet:

If r Is Nothing Then Set r = ActiveWindow.RangeSelection