Excel? keeping data sorted when adding new rows

  ShorN 13:41 29 May 05
Locked

Hi, hoping i can get some more help with excel!

Im creating a spreadsheet of data. It consisits of 15 columns of data. Five of these colums are totals in another column to create a priority list. (ie it totals the amounts from the five colums and the higher the total the higher priority it gets) (when i say priority i mean for my own purposes.)

Ive added approx 40 rows of data and i can then sort it to decend by the priority number so the highest prority is at the top.

but is there a way to keep it automatically sorted as i continue to ad rows of data?

Sorry if this seems confusing.!

TIA

  pj123 14:13 29 May 05

I run a Lottery syndicate and I have an excel spreadsheet which checks the draws for me and also enters the drawn numbers into another worksheet which then sorts them into the amount of times they have each been drawn? The formula/function I use is COUNTIF. It seems to do what you want. It is something like this:

=countif($a$4:$f$500,i4) and then copied down by the amount of rows. (in my case 49) so that i4 changes to i5, i6 etc.

You will obviously have to use your own cell references. Also I found it does have to use absolute cell references.

I am sure Vog will come up with a better solution, but mine works for what I want it to do.

  VoG II 20:03 29 May 05

The following Visual Basic for Applications code should do what you want.

Sub SortMe()

Dim Lastrow As Integer

Lastrow = ActiveSheet.Range("A65536").End(xlUp).Row - 1

Range("A1:O" & Lastrow).Select

Selection.Sort Key1:=Range("A2"), Order1:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub

To use this press ALT+F11 to open the Visual Basic Editor. Insert/Module and paste in the code. Exit the Visual Basic Editor.

To run it Tools/Macro/Macros, highlight SortMe and click the Run button. To make things easier you can place a button on the worksheet - View/Toolbars/Forms, click on the Button icon then on the worksheet drag to create the button. You may be prompted to choose a macro name (depending on your version of Excel). If not, right click the button and choose Assign Macro.

Notes:

1 This won't sort automatically as you enter rows - you will need to run it after adding one or more rows.

2 I've assumed that your data is in Columns A to O and that Row 1 contains headings.

3 I've assumed that the sort key is in Column A - if it isn't then you need to change this bit

Key1:=Range("A2")

to contain the correct column.

4 I think that it might be possible to use a Worksheet_Change macro to get this to run automatically when you enter new data but my experioence suggests that this might be quite slow to run.

  ShorN 21:16 29 May 05

Im not at work at the moment, so ill try it out and report back!

Thanks very much for all the help!

  ShorN 11:22 02 Jun 05

Right ive just got round to testing this and im having a few problems.

My data is from A4:O4 there is immediate headers above this and a title in the top 2 rows.
My sort key is also in row J.

I edited the code to this:

Sub SortMe()

Dim Lastrow As Integer

Lastrow = ActiveSheet.Range("A65536").End(xlUp).Row - 1

Range("A4:O" & Lastrow).Select

Selection.Sort Key1:=Range("J4"), Order1:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End Sub

I applied this and it works but it doesn't include the last row of data, I tried adding a few more rows and it sorts it all bar thye last row of data.

Any ideas or help?

TIA

  VoG II 11:45 02 Jun 05

Change

Lastrow = ActiveSheet.Range("A65536").End(xlUp).Row - 1

to

Lastrow = ActiveSheet.Range("A65536").End(xlUp).Row

  ShorN 13:47 02 Jun 05

Thats great it now works!

And it is sure to say me alot of time.
I Also added the button like you suggested.

your help was very much appreciated.
Thanks for your help aswell pj123 but VoG has hit the nail on the head.

Cheers.

This thread is now locked and can not be replied to.

How to get Windows 10 for free | How to install Windows 10: There is still a way to avoid paying…

1995-2015: How technology has changed the world in 20 years

Hands-on with the Star Wars fighting drones you can fly yourself

15 macOS Sierra tips | How to use macOS Sierra: Secret tricks and best new features in Apple's new…