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.

HansVogelaar's avatar
HansVogelaar
MVP
Oct 02, 2023

Yousef70 

Sorting a list while you are entering data would be very confusing - a row would jump to another location when you edit it.

As an alternative, you could write VBA code to sort the list when you open the workbook and/or when you activate the sheet the list is on.

Would that be OK?

Yousef70's avatar
Yousef70
Brass Contributor
Oct 05, 2023

HansVogelaar 

 

Dear Hans …

 

what I mean in automating sorting is when I start a new month with my provision sheet I have to change the numbers according to each month expenses , thus ,  instead of sorting it manually I am looking for it to be done automatically … is it possible ? open to all suggestions  

    • 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