Solved: Sort range by active column

[jmenche]

Hi,

I have a spreadsheet that has 4 worksheets with similar ranges. Each range has 4 columns with the first one being descriptive and the other 3 having a rank. I wanted to add in sorting functionality based on the 3 columns with ranks. I did this by adding a transparent rectangle to each column heading and attaching a macro that sorts the range by that column ascending.

I realized that there are 12 subroutines now that are very similar. Is there a way to do this better using relative references to an activecell?

Thanks

[mvidas]

Hello,

Try putting this in your ThisWorkbook object, should accomplish what you're looking for, and you can get rid of your rectangles and other code:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Row = 1 And Target.Column >= 2 And Target.Column <= 4 Then
Sh.Cells.Sort Key1:=Target.Cells(1, 1).EntireColumn, Order1:=xlAscending, Header:=xlYes
End If
End Sub

Matt

[jmenche]

Thanks!! I knew there was an easier way :-)

[mvidas]

Glad to help :)
If this is all set, you can mark the thread solved by going to the Thread Tools at the top of the thread and selecting "Mark Thread Solved".

Let me know if you want any modifications made!
Matt