How can I sort data automatically?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

surekha - 09 Apr 2007 09:34 GMT

I want data to be sorted automatically while typing on the screen?

for example I want number to come serialy if i type 4 it will get
automatically shifted in between 3 and 5 which is already type in the screen.

Reply to this Message

Max - 09 Apr 2007 12:36 GMT

One play, adapted from a past posting by Andy Brown

Right-click on the sheet tab > choose "View Code"
Then copy n paste this into the code window (white space)
'------
Private Sub Worksheet_Change(ByVal Target As Range)
Range("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub
'--------
Press Alt+Q to get back to Excel.
Test it out by typing numbers in A1 down

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> I want data to be sorted automatically while typing on the screen?
>
> for example I want number to come serialy if i type 4 it will get
> automatically shifted in between 3 and 5 which is already type in the screen.

Reply to this Message

David McRitchie - 09 Apr 2007 13:35 GMT

You won't be able to continue typing into the next
column if the row jumps, would suggest you do
the sort when the worksheet is activated

Sorting triggered by an Event Macro
http://www.mvps.org/dmcritchie/excel/sorting.htm#activatge

which includes an event macro for worksheet activation and
another event macro to activate at will with a doubleclick.

also see additional comments in
http://www.mvps.org/dmcritchie/excel/event.htm#ws_activate

---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

> One play, adapted from a past posting by Andy Brown
>
> Right-click on the sheet tab > choose "View Code"
> Then copy n paste this into the code window (white space)

Reply to this Message

David McRitchie - 09 Apr 2007 13:59 GMT

correction for typo (#activate fragment-id was mistyped)
http://www.mvps.org/dmcritchie/excel/sorting.htm#activate

Reply to this Message

RagDyeR - 09 Apr 2007 17:16 GMT

One approach is to use a formula in a "helper" column.

The helper column will sort itself, automatically, as numbers are entered
into the "main" column.

With numbers being entered in Column A,
Enter this formula in B1:

=IF(A1,SMALL(A:A,ROWS($1:1)),"")

And copy down as needed.

Signature

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Reply to this Message