Automate data sorting with double click event?

[Humble Newbie]

Morning!

I have a sheet that is made up of 2 sections. The top section lists 8 different types of event in chronological order. The bottom half lists the same 8 events but in rank order. A blank row separates each of the events for clarity.

Each of the 8 events has between 5 and 70 rows of data, and 11 columns. Rows are added on an ongoing basis.

What I'd like to avoid if possible, is having to input the information in both sections and being able to view the data in chronological and rank order as quickly as possible.

I am aware of the sort function, but I've found it very tedious and time consuming to define the sort order every time, when the sort order's always the same.

Ideally I'd like to input the information in the chronological order section of the sheet only, and when a particular cell or row for an event is double clicked I then see the event sorted in rank order and then reversed again to chronological order when I double click.

Any suggestions would be most welcome though, and I'll gladly post more info if this is required.

Thank you!

 

[BrianB]

This code is to sort a table depending on which column is doubleclicked.
It goes into the code module for the worksheet (rightclick tab then View Code). It is set up to be easily adaptable. The main thing is to set the HeaderRow correctly and have nothing underneath the table (so can add new records without changing the code).

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
    Dim HeaderRow As Long
    Dim ClickColumn As Integer
    Dim LastRow As Long
    Dim LastCol As Integer
    Dim TableRange As Range
    Dim SortKey As String
    '-------------------------------------------------
    Application.Calculation = xlCalculationManual
    HeaderRow = 1   ' row containing column headings
    ClickColumn = ActiveCell.Column
    '- set table range
    LastRow = ActiveSheet.Range("A65536").End(xlUp).Row
    LastCol = ActiveSheet.Cells(HeaderRow, 1).End(xlToRight).Column
    Set TableRange = ActiveSheet.Range(Cells(HeaderRow, "A"), Cells(LastRow, LastCol))
    '---------------------------------------------------------------------------
    '- sort on current column
    SortKey = Cells(HeaderRow + 1, ClickColumn).Address
    TableRange.Sort Key1:=Range(SortKey), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    '-----------------------------------------------------------------------
    '-finish
    Application.Goto reference:=TableRange.Cells(1, 1), Scroll:=True
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[Humble Newbie]

Brian - thanks a lot for your help, you've really made my day!

You understood my problem perfectly and your code works absolutely FANTASTIC!

 

[Humble Newbie]

Hi again Brian!

I hope you don't my asking you one more question?

I want to be able to display average values for the identical values of 6.2, 10, 13.1 and 26.2 shown in column E, based on the corresponding value in column G and return the result for each in a message box or in some other way, to avoid having data below the table, as per your suggestion.

Is this possible please?

Thanks once again.

 

[BrianB]

Sorry, I do not understand what you want (ie. " .. based on ...."). It may be best to put in another message. You will need to give an example of your calculation and what result you expect.

To me an average is just a total of numbers divided by how many there are.

However, you might consider putting your numbers ABOVE the table. As a "table person" I do this all the time (such as with column totals). I find this easier than having to trawl to the bottom each time anyway.

 

[Humble Newbie]

Hi Brian!

Sorry for the confusion. Column E contains numerical values, some of which are repeated in the column. Of the ones that are repeated, it's the 6.2, 10, 13.1 and 26.2 values that I'm concerned. Simplified example is as follows:

Col E Col G
3.0 0:21:02
6.2 0:45:00
6.2 0:50:00 expected ave 0:47:30
8.0 1:04:00
10 1:10:00
10 1:15:00 expected ave 1:12:30
13.1 1:30:00
13.1 1:40:00 expected ave 1:35:00
26.2 3:00:00
26.2 3:30:00 expected ave 3:15:00

What I'm after is an average to be shown say in G3,G4,G5 & G6 above the table for the 6.2, 10, 13.1 and 26.2 times.

As the table is normally shown in chronological order and not in column E order, I'm stumped as to how I can do this!

Thanks again.