Excel Sort function on the active range

%3CLINGO-SUB%20id%3D%22lingo-sub-2172725%22%20slang%3D%22en-US%22%3EExcel%20Sort%20function%20on%20the%20active%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2172725%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20guys%20and%20girls%2C%20I'm%20new%20here%20and%20I'm%20not%20an%20inexperienced%20Excel%20user.%20BUT%2C%20I%20have%20a%20challenge%20with%20a%20Table%20with%2011%20columns%20in%20where%20I%20like%20to%20automatically%20sort%20(ascending)%20the%20whole%20data%20(numbers)%20based%20on%20column%2010.%20Please%20keep%20in%20mind%20that%20practically%20weekly%20new%20rows%20with%20data%20are%20added%20to%20this%20table%20and%20I%20like%20the%20sorting%20to%20be%20done%20automatically.%20I've%20seen%20videos%20where%20they%20use%20the%20formula%20SORT.BY%20in%20another%20cell%20and%20the%20whole%20table%20will%20be%20sorted%20but%20copied%20starting%20in%20that%20new%20cell.%20I%20like%20the%20sorting%20to%20be%20happing%20automatically%20in%20the%20active%20table.%20As%20if%20I%20would%20click%20on%20the%20top%20pulldown%20of%20column%2010%20and%20choose%20sort%20manually.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2172725%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2172747%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Sort%20function%20on%20the%20active%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2172747%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F981331%22%20target%3D%22_blank%22%3E%40FrankWitsel%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20to%20sort%20the%20data%20in%20place%2C%20you%20could%20use%20VBA%20code%20to%20sort%20the%20table%20when%20the%20workbook%20is%20opened%2C%20and%20if%20desired%20also%20when%20you%20switch%20to%20the%20relevant%20sheet%20from%20another%20sheet%20in%20the%20workbook.%3C%2FP%3E%0A%3CP%3E(Sorting%20automatically%20as%20data%20are%20being%20added%2Fedited%20is%20not%20a%20good%20idea%2C%20it%20would%20look%20very%20confusing.)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20do%20so%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E1)%20Press%20Alt%2BF11%20to%20activate%20the%20Visual%20Basic%20Editor.%3C%2FP%3E%0A%3CP%3ESelect%20Insert%20%26gt%3B%20Module.%3C%2FP%3E%0A%3CP%3ECopy%20the%20following%20code%20into%20the%20module%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3ESub%20SortData()%0A%20%20%20%20With%20Worksheets(%22Sheet1%22)%0A%20%20%20%20%20%20%20%20.Range(%22A1%22).CurrentRegion.Sort%20Key1%3A%3D.Range(%22J1%22)%2C%20Header%3A%3DxlYes%0A%20%20%20%20End%20With%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EChange%20the%20name%20of%20the%20sheet%20to%20the%20one%20containing%20the%20table%2C%20and%20change%20A1%20and%20J1%20if%20the%20table%20does%20not%20start%20in%20A1.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E2)%20Double-click%20ThisWorkbook%20under%20Microsoft%20Excel%20Objects%20in%20the%20Project%20Explorer%20pane%20on%20the%20left.%3C%2FP%3E%0A%3CP%3ECopy%20the%20following%20code%20into%20the%20ThisWorkbook%20module%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20Workbook_Open()%0A%20%20%20%20Call%20SortData%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EThis%20will%20sort%20the%20data%20when%20the%20workbook%20is%20opened.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E3)%20Double-click%20the%20relevant%20worksheet%20in%20the%20Project%20Explorer.%3C%2FP%3E%0A%3CP%3ECopy%20the%20following%20code%20into%20the%20worksheet%20module%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Activate()%0A%20%20%20%20Call%20SortData%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EThis%20will%20sort%20the%20data%20when%20you%20switch%20to%20the%20worksheet%20from%20another%20sheet%20in%20the%20same%20workbook.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E4)%20Switch%20back%20to%20Excel.%3C%2FP%3E%0A%3CP%3ESave%20the%20workbook%20as%20a%20macro-enabled%20workbook%20(.xlsm).%3C%2FP%3E%0A%3CP%3EMake%20sure%20that%20you%20allow%20macros%20when%20you%20open%20the%20workbook.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi guys and girls, I'm new here and I'm not an inexperienced Excel user. BUT, I have a challenge with a Table with 11 columns in where I like to automatically sort (ascending) the whole data (numbers) based on column 10. Please keep in mind that practically weekly new rows with data are added to this table and I like the sorting to be done automatically. I've seen videos where they use the formula SORT.BY in another cell and the whole table will be sorted but copied starting in that new cell. I like the sorting to be happing automatically in the active table. As if I would click on the top pulldown of column 10 and choose sort manually. 

3 Replies

@FrankWitsel 

If you want to sort the data in place, you could use VBA code to sort the table when the workbook is opened, and if desired also when you switch to the relevant sheet from another sheet in the workbook.

(Sorting automatically as data are being added/edited is not a good idea, it would look very confusing.)

 

To do so:

 

1) Press Alt+F11 to activate the Visual Basic Editor.

Select Insert > Module.

Copy the following code into the module:

Sub SortData()
    With Worksheets("Sheet1")
        .Range("A1").CurrentRegion.Sort Key1:=.Range("J1"), Header:=xlYes
    End With
End Sub

Change the name of the sheet to the one containing the table, and change A1 and J1 if the table does not start in A1.

 

2) Double-click ThisWorkbook under Microsoft Excel Objects in the Project Explorer pane on the left.

Copy the following code into the ThisWorkbook module:

Private Sub Workbook_Open()
    Call SortData
End Sub

This will sort the data when the workbook is opened.

 

3) Double-click the relevant worksheet in the Project Explorer.

Copy the following code into the worksheet module:

Private Sub Worksheet_Activate()
    Call SortData
End Sub

This will sort the data when you switch to the worksheet from another sheet in the same workbook.

 

4) Switch back to Excel.

Save the workbook as a macro-enabled workbook (.xlsm).

Make sure that you allow macros when you open the workbook.

 

There's also a new Dynamic Array function SORT which you could use.

Keep the existing sheet, with the data established as an official Excel Table.
Then use SORT on a second sheet to produce a sorted version of that Table
Do all your additional rows in the original (unsorted) table. They will automatically be added to and resorted in the second version. It's basically separating the raw data (Input) from the Output.

 

Here are two resources to learn more about SORT and some other of the Dynamic Array functions.

https://www.youtube.com/watch?v=9I9DtFOVPIg

 

https://exceljet.net/excel-functions/excel-sort-function

 

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, bu...
Dag Hans, dankjewel voor je uitgebreid antwoord en alhoewel ik Visual Basic nog nooit heb geprobeerd ga ik jouw advies wel proberen. Ik begrijp ook je kritiek, maar maak je geen zorgen, de toegevoegde regels zijn na invoering in 95% van de gevallen de regels die altijd onderaan staan. Het is een voorraadoverzicht met LOT en THT gegevens die ik in het overzicht wil bewaren maar die na uitlevering steeds verder in waarde dalen en die wil ik zoveel mogelijk naar boven verschuiven totdat ze op '0' staan en uit beeld zijn en onderaan alleen de rijen tonen waar nog voorraad van staat. Gr. Frank PS of heb jij een beter idee...? en dan heb ik het niet over voorraadboekhouding van Exact of zo, maar een betere oplossing in Excel.