How to sort by date on double click on the column?? [SOLVED]

[rkspeaks]

Hi All,


I have my data like the below mentioned in our Sales Report. We make at least 100 sales entries per salesman daily and we have 50 salesmen. So we are facing difficulty searching for the current date's sales going hundreds of rows deep down.


Sno, Date, Salesman, Client, Location etc

1 8/12/13 Ram Cat NYC

2 8/13/13 Raj Ron Jefferson

3 8/14/13 Shekhar Jose Tampa

4 8/15/13 Vijay Vesta Reno


So, what is the best "Event" to sort by Date column so that I can see the latest Date's data on Top? I am thinking of sort on the event "Double click on the Date column".

Can somebody provide a piece of VBA poetry to handle my situation. Which is the best Event to sort on whether a 1)On Worksheet Open, 2)On Double Click on the column, or 3)Going to the last non-entry cell(which will also be a solution to my problem).


Please advise me which is the safest?. A ton of thanks in advance.

RK

 

[Smallman]

Hi rkspeaks


Cool Avatar!!!! OK lets say your dates are in Col 2 and you have a total of 5 Cols, starting in Row 1. Put the following in worksheet Object module and it should sort your problem. It fires on double click. I will post a workbook to show workings shortly.

[pre]

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Columns(2)) Is Nothing Then
Range("A2", Range("E65536").End(xlUp)).Sort [B2], 2
End If
End Sub

[/pre]

Here is the link as promised. Good luck with your project : )


http://rapidshare.com/files/3633025958/DoubleClickEvent.xlsm


Take care


Smallman

 

[rkspeaks]

Smallman, Thank you. Can you instruct me on How to use the code carefully? Because the data range is dynamic as it is a Sales data. And I have 19 columns till column "S" with data validations and formulas in some columns. It should sort only the data with out missing the functionality. Thank you very much.

 

[Smallman]

Hi RKSpeaks


See the link above for a working copy of how a process might work. Change the E65536 to S65536 for your dataset. The above coding is dynamic and will move with your data set. That assumes you don't have a larger dataset than 65.5K rows long. It assumes your dates are on Col B. If you can't get it working post your workbook and exclude any sensitive data if it is an issue (keep the same structure though).


Take care


Smallman

 

[rkspeaks]

Smallman, it's just working fine. Thanks a lot.

 

[Smallman]

No worries mate - see you next time at Chandoo!!!


Smallman