Forum Discussion

Yousef70's avatar
Yousef70
Brass Contributor
Oct 02, 2023
Solved

Automate sorting

Dear Excel community   Kindly , need help to automate sorting in a vertical column with numbers as in , if a number is changed whether smaller or larger to be sorted out according to what originall...
excel
  • HansVogelaar's avatar
    HansVogelaar
    Oct 07, 2023

    Yousef70 

    As I mentioned, it is very confusing if a row jumps away, possibly out of sight, the moment you edit an amount.

    I'd sort the sheet:

    • When you activate the sheet
    • When you open the workbook.

    Press Alt+F11 to activate the Visual Basic Editor.

    Select Insert > Module.

    Copy the following code into the module:

    Sub SortSheet()
    Application.ScreenUpdating = False
    With Worksheets("Sheet1")
        .Range("A1").CurrentRegion.Sort Key1:=.Range("B1"), Order1:=xlDescending
    End With
    Application.ScreenUpdating = True
End Sub

    Change Sheet1 to the name of the sheet with your data.

    Double-click the sheet under Microsoft Excel Objects in the Project Explorer pane on the left.

    Copy the following code into the worksheet module:

    Private Sub Worksheet_Activate()
    Call SortSheet
End Sub

    Double-click ThisWorkbook in the Project Explorer pane.

    Copy the following code into the ThisWorkbook module:

    Private Sub Workbook_Open()
    Call SortSheet
End Sub

    Switch back to Excel.

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

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

Yousef70's avatar
Yousef70
Brass Contributor
Oct 07, 2023

HansVogelaar 

 

Dear Hans …

Below is two columns from the excel sheet I use :-

1

rent1500
2loans659
3accounting400
4electrical bill300
5employment210
6visa payment

120

 

7system maintenance 50
8hospitality15
9packaging15
10cleaning

13

 

lets say that I want to change electrical bill to 450  , visa payment to 625 , and loans to 275 

What I am looking for is as soon as I enter the new number it sorts automatically to were it should be , if possible in any way possible 

 

thanks in advance

HansVogelaar's avatar
HansVogelaar
MVP
Oct 07, 2023

Yousef70 

As I mentioned, it is very confusing if a row jumps away, possibly out of sight, the moment you edit an amount.

I'd sort the sheet:

  • When you activate the sheet
  • When you open the workbook.

Press Alt+F11 to activate the Visual Basic Editor.

Select Insert > Module.

Copy the following code into the module:

Sub SortSheet()
    Application.ScreenUpdating = False
    With Worksheets("Sheet1")
        .Range("A1").CurrentRegion.Sort Key1:=.Range("B1"), Order1:=xlDescending
    End With
    Application.ScreenUpdating = True
End Sub

Change Sheet1 to the name of the sheet with your data.

Double-click the sheet under Microsoft Excel Objects in the Project Explorer pane on the left.

Copy the following code into the worksheet module:

Private Sub Worksheet_Activate()
    Call SortSheet
End Sub

Double-click ThisWorkbook in the Project Explorer pane.

Copy the following code into the ThisWorkbook module:

Private Sub Workbook_Open()
    Call SortSheet
End Sub

Switch back to Excel.

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

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

Resources