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

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:D" & 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




"kevin" wrote in message
...
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

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" wrote in message
...
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

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