Solved: Excel VBA Sort with user Input

[jsabjc]

Hi, I'm trying to sort a large range of data by the column selected by the user. In other words, I have named the sort range and want the user to be able to click on any column to select which they want to sort. Here's the code I've tried thus far but can't get the absolute cell reference to return just the cell value.


Dim SortCol As Range
' Use an Input box to select the column to sort

Set SortCol = Application.InputBox("Select the column to sort", "Sort-Box", 0, , , , , 8)
Range("ALLData").Select
Selection.Sort Key1:=Range("SortCol"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("C9").Select
End Sub

[domfootwear]

Hi, I'm trying to sort a large range of data by the column selected by the user. In other words, I have named the sort range and want the user to be able to click on any column to select which they want to sort. Here's the code I've tried thus far but can't get the absolute cell reference to return just the cell value.


Dim SortCol As Range
' Use an Input box to select the column to sort

Set SortCol = Application.InputBox("Select the column to sort", "Sort-Box", 0, , , , , 8)
Range("ALLData").Select
Selection.Sort Key1:=Range("SortCol"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("C9").Select
End Sub


Try this code:


Sub sort()
Dim SortCol As Range
' Use an Input box to select the column to sort

Set SortCol = Application.InputBox("Select the column to sort", "Sort-Box", 0, , , , , 8)
Range("ALLData").Select
Selection.sort Key1:=SortCol, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("C9").Select
End Sub

[mdmackillop]

Try to avoid Selecting ranges. It slows down code execution.

Sub sort()
Dim SortCol As Range
' Use an Input box to select the column to sort

Set SortCol = Application.InputBox("Select the column to sort", "Sort-Box", 0, , , , , 8)
Range("ALLData").sort Key1:=SortCol, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

[jsabjc]

Thanks all, that worked!!

[jsabjc]

Okay, one more question. How do I get it to end up in the colum selected after the sort?

[mdmackillop]

Depends where you want to go.
Either


SortCol.Select
'or
Cells(1,SortCol.Column).Select

[lucas]

I thought you wanted to click on the column to sort?

In the attachment you click on any of the column head cells in row 2 to sort by that column:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Row <> 2 Then Exit Sub
Rows("3:65536").Sort Key1:=Cells(3, Target.Column), Order1:=xlAscending, Header:=xlNo
End Sub

[domfootwear]

I thought you wanted to click on the column to sort?

In the attachment you click on any of the column head cells in row 2 to sort by that column:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Row <> 2 Then Exit Sub
Rows("3:65536").Sort Key1:=Cells(3, Target.Column), Order1:=xlAscending, Header:=xlNo
End Sub

Dear Lucas,

This code verry good !

Thanks so much for your code

[James_IRS]

This does not work for me. I have the user selecting 2 Columns of data from one sheet, and another 2 columns of data from another sheet. I would then like the first set of data put in alphabetical order based on the data in the first column, and the same for the second set of data. Then I would like to display both sets of data side-by-side for comparison. Please Help. I wouldn't think this should be so difficult.