sort dynamic range on active cell

[kevin]

Hi there

is there anyway to sort a dynamic range (ie amount of columns and rows may
vary) based on the column of the active cell. to make things even more
complicated i would like to have a button that when you click it the data is
sorted ascending and if you click it again it is descending)

Examle:
Name, age, class, height = col headings
if i click a in any cell in col b (age) then click button it sorts data
ascending, click again and its descending
If i then select a cell in col d (height) i can sort that col based on
active cell being in it

i hope there is a neat way of doing this.

Appreciate any help in this regard

Thanks
Kev

 

[JLGWhiz]

You can try this to see if it works like you expect. I used Range("F2") as
the control range. You can change it to another obscure range by canging
the cell reference in the code.

Sub CommandButton1_Click()
Dim rng As Range, lr As Long
Dim sh As Worksheet
Set sh = ActiveSheet
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A2" & lr)
sel = Selection.Address
If Range("F2").Value <> "Down" Then
rng.Sort Range(sel), xlAscending
sh.Range("F2") = "Down"
Else
rng.Sort Range(sel), xlDescending
sh.Range("F2") = "Up"
End If
End Sub

 

[JLGWhiz]

I assumed you knew how to put a command button from the Control Toolbox on
the sheet and then put the code in the sheet code module. That will be
necessary for you to utilize the code provided.

 

[kevin]

Thanks JLGWhiz

I've been searching the boards for code to run that private sub you posted
when i click on the command button but no luck. how do i call the procedure.
do need option explicit or to dim stuff as public.

Hope you can help

Kev