help with sort on active cell

[kevin]

hi is there anyway to sort a range but the column to be sorted is based on
which cell is active ie if i have 3 cols - name, age, height i need to click
a button that sorts the col of whatever cell is currently selected. ideally i
would like to be able for the button to sort the other way ie descending if
clicked again. To complicate things the range is dynamic

Hope you guys can help

Thanks
kevin

 

[Bernie Deitrick]

Kevin.

The code below will sort the first three columns based on the active cell, with the region starting
in A1, with headers in row 1.

Insert a commandbutton off the control toolbox commandbar to sude this code.

HTH,
Bernie
MS Excel MVP


Public SortAsc As Boolean
Public SortDesc As Boolean
Public myCol As Integer

Private Sub CommandButton1_Click()
Dim myC As Range
Dim myR As Range

Set myR = Range("A1").CurrentRegion
Set myC = Selection

If myC.Cells.Count > 1 Then
MsgBox "Select a single cell only."
Exit Sub
End If

If Intersect(myR, myC) Is Nothing Then
MsgBox "Select a cell within " & myR.Address(False, False)
Exit Sub
End If

If myCol = 0 Or myCol <> myC.Column Then
myCol = myC.Column
SortAsc = False
SortDesc = False
End If

If Not SortAsc And Not SortDesc Then
SortAsc = True
myR.Sort myC, xlAscending, header:=xlYes
ElseIf SortAsc Then
SortAsc = False
SortDesc = True
myR.Sort myC, xlDescending, header:=xlYes
Else
SortAsc = True
SortDesc = False
myR.Sort myC, xlAscending, header:=xlYes
End If

End Sub

 

[JLGWhiz]

There is no need for multiple postings. See your otiginal post.

 

[kevin]

Agreed. I came back to the discussion board and didn't see my original post
so thought it may have not gone through and submitted another. apologies

Thanks for the help Bernie really appreciate it