01-31-2019 01:28 AM
01-31-2019 01:28 AM
How do I automatically (without out pressing the sort button) sort data in ascending order?
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.
01-31-2019 02:19 AM
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.
01-31-2019 03:18 AM
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.
01-31-2019 04:15 AM
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.
01-31-2019 03:35 PM
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?
02-01-2019 01:32 AM
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.
by patharvey-001 on May 06, 2020
by CalebLPerry on September 07, 2019
by Bill Jelen on February 26, 2020
by Jee Soo Han on November 04, 2019
by cuong on July 30, 2019