Forum Discussion

Copper Contributor

Jan 31, 2019

Automatically sort data HELP!

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 ascendi...

excel

Excel on Mac

Formulas and Functions

office 365

Rich99

Iron Contributor

Jan 31, 2019

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

mjkjones

Copper Contributor

Jan 31, 2019

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

Rich99
Iron Contributor
Jan 31, 2019
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
macrotest.xlsx8 KB
- mjkjones
  Copper Contributor
  Jan 31, 2019
  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
  - Rich99
    Iron Contributor
    Feb 01, 2019
    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