Feb 28 2021 10:50 AM
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.
Feb 28 2021 11:05 AM
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.
Feb 28 2021 11:51 AM - edited Feb 28 2021 11:53 AM
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
Mar 01 2021 08:15 AM