Excel Data Sort: Microsoft Excel 2003

Excel Data Sort

Hi Everyone,

I Have a worksheet containing stock details. I want to sort data realtime by just clicking on the table headers. I know how to sort the data normally. But that way i have to run the sort for every header that i require and i waste a lot of time doing the sort manually

My worksheet contains the following headers...
Sr.No., Description, Package,active stock, order treshold, container, Stock update Date

Normally i sort my data on either of these headers - active stock, order treshold, container, Stock update Date

Can someone give me the macro to do this sort?

Instant Access to this Solution

Plus...

30 Day FREE access, no risk, no obligation

Collaborate with the world's top tech experts

Unlimited access to our exclusive solution database

Never be left without tech help again

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

"The time we save is the biggest benefit of Experts Exchange to Warner Bros. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange." Mike Kapnisakis, Warner Bros.

"Our team likes having a resource that is more secure than just using Google and most experts using this service really know their stuff. It's nice to look here first versus using Google." Dayna Sellner, Lockheed Martin

"Anytime that I've been stumped with a problem, 9 out of 10 times Experts Exchange has either the accepted solution or an open discussion of the potential solution to the problem." Kenny Red, eBay Inc.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.

Screenshot of Experts Exchange Knowledgebase

Need individual assistance?

Our experts are ready to help.

If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.

Want to learn from the best?

Read articles from industry experts.

Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.

Working on a long term project?

Store your work and research.

Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.

Join the Community

Give a Little. Get a Lot.

Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.

If you put the attached code in the worksheet module of the worksheet containing the table you want to sort, you can just double click the header text to sort that column.

Open VBA editor (ALT+F11)
Open the project explorer if it's not already open (Ctrl+R)
Locate the VBA project of your excel file and expand it (pressing the '+' icon)
For each of the worksheets in your workbook you will have a module called Sheet1, Sheet2 etc. Double click the worksheet you want to implement easier sorting for
Paste the below code into the module
Modify the line that says [Const cstrHeadRngAdr As String = "A1:E1"] to where your header is located, for example your header can be located in the range "C3:X50" you would then change the line to read [Const cstrHeadRngAdr As String = "C3:X50"]
Goto Excel again (ALT+F11)
Find your worksheet at double click the header column you want to sort by..

Tell me how this works out for you, there are many ways of doing this sort of thing.

Private Sub Worksheet_BeforeDoubleClick(ByVal rngTarget As Range, _
 ByRef boolCancel As Boolean)
    On Error GoTo ExitProc:
    
    Const cstrHeadRngAdr As String = "A1:E1" 'Define the header range
    Dim rngHead As Range
    Dim rngToSort As Range
    
    Set rngHead = rngTarget.Parent.Range(cstrHeadRngAdr)
    If Intersect(rngTarget, rngHead) Is Nothing Then
        GoTo ExitProc:
    End If
    Set rngToSort = rngHead.Resize(Me.UsedRange.Row + Me.UsedRange.Rows.Count)
    rngToSort.Sort Key1:=rngTarget, Order1:=xlAscending, Header:=xlYes
ExitProc:
End Sub

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:

Select all Open in new window

Question

Excel Data Sort

Subscribe now for full access to Experts Exchange and get

Instant Access to this Solution

Microsoft Excel 2003

Microsoft Excel Spreadsheet Software

Microsoft Visual Interdev

Trusted by hundreds of thousands everyday for fast, accurate and reliable tech support.

See what Experts Exchange can do for you.

Got a question?

We've got the answer.

Need individual assistance?

Our experts are ready to help.

Want to learn from the best?

Read articles from industry experts.

Working on a long term project?

Store your work and research.

What Makes Experts Exchange Unique?

Trusted by the world's most respected brands.

Faithfully serving IT professionals since 1996.

Related Solutions

Free Tech Articles

Cloud Class Webinars

Join the Community

Give a Little. Get a Lot.

Answers

3 Ways to Join

The Experts

The Experts

Testimonials

Testimonials

Testimonials

Business Clients

Business Clients

In the Press

In the Press

In the Press