Automatic Data Sort?

S

Scott

Is there a way to automatically sort a series of data?

For instance, cells A1:A144 have a numeric value. In Column B1:B144, I'd
like to sort those numeric values in ascending order. If I update one of the
numeric values in A1:A144, is there a way that B1:B144 will automatically
update and sort without me having to put a manual ascending sort order to it?

Thanks!
 
D

Don Guillett

You can use a worksheet_change event macro in the sheet module but do you
really want to sort with EACH entry?
 
S

Scott

Yes. The A Column has numeric data. Some may be duplicates. Not sure how
many duplicates I will have as they are rounded to one decimal. But if one
of the values in Column A changes, that'll change that value's ranking in
Column B. Hopefully there won't be any duplicates, but with 144 values,
that's not a guarantee.
 
G

Gord Dibben

I'm not sure, but I think Don may be cautioning you about auto-sorting which
is easily done using event code.

The problem with automatic sorting is error checking bad data entries.

Once you hit the Enter key the sort is done and makes it more difficult to
find an error.


Gord Dibben MS Excel MVP
 
G

Gary''s Student

If there are duplicates, then instert the following event macro in the
worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
Set r1 = Range("A1:A144")
Set r2 = Range("B1:B144")
If Intersect(Target, r1) Is Nothing Then Exit Sub
Application.EnableEvents = False
r1.Copy r2
r2.Sort Key1:=Range("B1")
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top