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?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
How to sort columns by clicking on a cell value?
- Thread starter sigil
- Start date
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.
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.
Upvote
0
VoG
Legend
- Joined
- Jun 19, 2002
- Messages
- 63,650
Try this: right click the sheet tab, select View Code and paste in
Double click in a column to sort by it. Double click in the same column to reverse the sort.
Code:
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 SubDouble click in a column to sort by it. Double click in the same column to reverse the sort.
Upvote
0
Try
Double click to sort it.
but dang, VoG's code is always better than mine
lol learninggg xD
Code:
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 SubDouble click to sort it.
but dang, VoG's code is always better than mine
lol learninggg xD
Upvote
0
Similar threads
- Question
- Question
