How to sort columns by clicking on a cell value?

[sigil]

I have several columns of data. I would like to set it up so I can sort it by clicking on one of the cells, or running VBA code that takes the current selected cell's position, does an A-Z sort based on the column of the selected cell, and takes the user to the selected cell's new position in the newly sorted column. How can I do this?

 

[kpark91]

How come you don't want to use autofilter function provided by excel?
There are more things you could do with it then just sort it.

 

[sigil]

Because Autofilter doesn't take me to the new position of the cell that was selected before the sort.

Basically, I have column A & column B. If it's sorted by A, so I'm looking at all the values in B that correspond to the same value in A, for any one of those B values I want to be able to see all the values in A that correspond to it.

 

[kpark91]

So you want the columns to be sorted independently from one another?

which means you don't care if the row set of values are mixed up?

and one last question: how many rows of data do you have?

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Static MySortType As Integer, x As Variant
Cancel = True
x = Target.Value
If MySortType = 0 Then
    MySortType = xlAscending
ElseIf MySortType = xlAscending Then
    MySortType = xlDescending
ElseIf MySortType = xlDescending Then
    MySortType = xlAscending
End If
Target.CurrentRegion.Sort key1:=Target, order1:=MySortType, Header:=xlYes
Columns(Target.Column).Find(x).Select
End Sub

Double click in a column to sort by it. Double click in the same column to reverse the sort.

 

[kpark91]

Try

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Application.ScreenUpdating = False
    Dim LR As Long, i As Long, j As Long, temp As Variant, val As Variant, c As Long
    
    c = Target.Column
    LRA = Cells(Rows.count, c).End(xlUp).Row
    val = Target.Value
    
    For i = 1 To LR - 1
        For j = i To LR
            If Cells(i, c).Value > Cells(j, c).Value Then
                temp = Cells(i, c).Value
                Cells(i, c).Value = Cells(j, c).Value
                Cells(j, c).Value = temp
            End If
        Next j
    Next i
    
    j = 0
    
    For i = 1 To LRA
        If Cells(i, c).Value = val Then
            Application.Goto reference:=Cells(i, c), scroll:=True
            Exit For
        End If
    Next i
            
    Application.ScreenUpdating = True
End Sub

Double click to sort it.
but dang, VoG's code is always better than mine lol learninggg xD

 

[sigil]

Works perfectly!

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Static MySortType As Integer, x As Variant
Cancel = True
x = Target.Value
If MySortType = 0 Then
    MySortType = xlAscending
ElseIf MySortType = xlAscending Then
    MySortType = xlDescending
ElseIf MySortType = xlDescending Then
    MySortType = xlAscending
End If
Target.CurrentRegion.Sort key1:=Target, order1:=MySortType, Header:=xlYes
Columns(Target.Column).Find(x).Select
End Sub

Double click in a column to sort by it. Double click in the same column to reverse the sort.