Automatically sort data HELP!

Copper Contributor

Hey guys

 

Question 1: 

How do I automatically (without out pressing the sort button) sort data in ascending order?

 

Question 2:

Alot of my values will be 0, and therefore if I sort in ascending order, they will stay at the top of the list. How can I automatically sort, in ascending order, but keep the cells with 0 at the bottom of the column. e.g. in the below example, how do I sort so that Player 1 - 4 remains at the bottom?

 

PLAYER 1.      0

PLAYER 2.      0

PLAYER 3.      0

PLAYER 4.      0

PLAYER 5.      2

PLAYER 6.      10

PLAYER 7.      12

 

Thanks you very much. 

Kind Regards

Matthew

5 Replies

Hi,

One method would be to apply conditional formatting to your values with zero values a different colour from your other values. Then sort your data using custom sort applying two levels one using colour as a sort and the other values, smallest to largest. See attached images.

 

Rich

 

 

Hi Rich,

 

The problem is I need to automatically sort the data.

I don't want to be going into the selection and manually sorting every time. 

 

Thanks

Matthew

Matthew,

 

In that case you will need to write some VBA to achieve what you want. I have attached a simple macro which will be a good starting point for you based on my previous solution but automated. There is a macro in sheet 1 that runs whenever a cell is changed in Column B (rows 1 to 18) that calls a macro (Mymacro) that will reorder your list. You will need to extend the conditional formatting to cover your range as well as change the row reference in the macro.

 

Rich

Thanks Rich, 

 

Where can I find the VBA code in that excel spreadsheet you sent?

 

When I open VIEW CODE, there doesn't seem to be anything in there?

 

Thanks

Matt

Matt,

The code is in two places, Sheet 1, Private Sub Worksheet_Change and Module 1, Sub Mymacro. see image attached which shows the code and project structure. 

 

Rich