How to sort Excel data automatically

Home More Videos




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.