How to sort Excel data automatically using VBA

A website visitor’s query: I’m wondering what function or code for a macro I need to do an automatic sort in MS Excel. I have names in column A and corresponding salaries in column B. I want columns A and B to be sorted in descending order automatically every time I enter a value in column B so that I don’t need to click on ‘Sort Descending’ in the tool bar.
Watch the video below to learn about the automatic sorting of Excel data using Excel VBA or a macro:

There are only a few geniuses who can remember all VBA code. Therefore we have used a simpler method: First we think through what we wish to achieve with our Excel data. The next step is to record the more difficult portions using the ‘Record New Macro…’ feature. Then we have a good look at the code, tweak it according to our needs and copy and paste it in our macro. Now our code for the above task looks like this:
Private Sub Worksheet_Change(ByVal Target As Range)
Range(“A2″).Select
Range(Selection, Selection.End(xlDown)).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateTextLength, AlertStyle:=xlValidAlertStop, _
Operator:=xlGreaterEqual, Formula1:=”1”
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = “Name”
.ErrorTitle = “Name”
.InputMessage = “Please do not leave blank. Enter some text.”
.ErrorMessage = “Please enter some text!”
.ShowInput = True
.ShowError = True
End With
Range(“B2″).Select
Range(Selection, Selection.End(xlDown)).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateDecimal, AlertStyle:=xlValidAlertStop, Operator _
:=xlGreaterEqual, Formula1:=”0.00”
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = “Salary”
.ErrorTitle = “Salary”
.InputMessage = “Please enter a number only!”
.ErrorMessage = “Did you enter a number? Pls check”
.ShowInput = True
.ShowError = True
End With
Dim erow As Long
erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Range(“B:B”).Select
Range(“A:B”).Sort Key1:=Range(“B2”), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
If Cells(erow – 1, 1).Offset(0, 1) = “” Then
Cells(erow – 1, 1).Offset(0, 1).Select
Else Cells(erow, 1).Select
End If
End Sub

The macro does the automatic sorting of the Excel data. It also provides data validation and the cursor is positioned properly for the next data entry.


10 thoughts on “How to sort Excel data automatically using VBA

  1. Jasim

    Hi,
    thank yo very much for the very helpful tutorial

    i modified the code little bit for my purpose as below
    ——————————————————————————-

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim erow As Long
    erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Range(“H2:H201”).Select
    Range(“A:I”).Sort Key1:=Range(“H2”), Order1:=xlAscending, Header:= _
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    If Cells(erow – 1, 1).Offset(0, 1) = “” Then
    Cells(erow – 1, 1).Offset(0, 1).Select
    Else: Cells(erow, 1).Select

    End If
    End Sub
    —————————————————————————————

    it is not working if there is any formula on column H
    since the data calculating through the formula on column H how i can modify the code accordingly

    i need to sort the value by Ascending on column H from H2 till H201 and all the rows (A:I) should sort accordingly

    the value on column H is getting from other sheets through some formulas

    Your kind help is highly appreciated

    Jasim

    Reply
  2. sanjeev

    Hi,
    Thank you for tutorial. Very beneficial.

    I need your help. I am trying to create a code to sort dates automatically in an ascending order. I want the entire row to move chronically with dates. Dates are in Column G and it is in a table from Column A to I. I try this code but nothing happened. Would you please look into this?

    Private Sub Worksheet_Change(ByValueTarget As Range)
    Dim erow As Long
    erow = ActiveSheet.Cells(Rows.Count, 1).End(x1Up).Offset(1, 0).Row
    Range(“G2:G100”).Select
    Range(“A:2”).Sort Key1:=Range(“G3”), Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=x1TopToBottom, DataOption1:=xlSortNormal
    If Cells(erow – 1, 1).Offset(0, 1) = “” Then
    Cells(erow – 1, 1).Offset(0, 1).Select
    Else: .Cells(erow, 1).Select
    End If
    End Sub

    Reply
  3. CS

    I would like the macro code to have the data to be sorted alphabetically. Column A is the names that need to be sorted Alphabetically and then the Data from Column B & C to go with it.

    Reply
  4. NISAR AHMAD

    Sir
    How can import a particular Colum from PDF to Excel file how ever i have many of PDF files and I want Import a particular Colum (which contains some value) from PDF to Excel Please Help for this formulla or Micro Please and Please

    Reply
  5. Vidya

    Hi

    Thank you for the brilliant tutorial!

    I am using the above code to sort my spreadsheet automatically.
    The data will be sorted out by column C

    However I have 7 columns to fill before sorting.
    How can I modify the above code so that I can enter the data in all columns before going to next line?

    erow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    If Cells(erow – 1, 1).Offset(0, 1) = “” Then
    Cells(erow – 1, 1).Offset(0, 1).Select
    Else: Cells(erow, 1).Select
    End If

    Reply
  6. Sandeep Gupta

    This is more shortest:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Range(“A1”).CurrentRegion.Sort _
    Key1:=Range(“B1”), Order1:=xlDescending, Header:=xlYes
    End Sub

    Reply
    1. Benny

      HI Sandeep,

      Appreciated for your shortest script, it is exactly how I want it.
      However, is it possible you can tweak it to have a sorting order range between eg. A8 to A32. Any rows beyond A32 is back to normal cells.
      ==============================================
      Private Sub Worksheet_Change(ByVal Target As Range)
      Range(“B8”).CurrentRegion.Sort _
      Key1:=Range(“A8”), Order1:=xlAscending, Header:=xlYes
      End Sub
      ==============================================

      Also thank you Dinesh providing this tutorial video

      Reply
  7. Bronwen

    Hi can anyone help me? Trying to automatically sort data by “High”, “Normal” “Low” in column E (priority level) then by date in column F (due date). Can anyone help with a macro for this?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *