Create Dynamic Sorting with Excel VBA

Objective

This VBA script will allow you to dynamically sort a dataset based on a selected column. The user will choose the column and sorting order (ascending or descending), and the macro will execute the sorting.

VBA Code

Sub DynamicSort()
    Dim ws As Worksheet
    Dim rng As Range
    Dim sortCol As String
    Dim sortOrder As XlSortOrder
    Dim lastRow As Long, lastCol As Long
    Dim sortKey As Range
    Dim userChoice As String
    Dim userOrder As String
    ' Set worksheet
    Set ws = ActiveSheet
    ' Find the last used row and column
    lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
    ' Define the dataset range (excluding headers)
    Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))
    ' Ask the user for column letter to sort by
    userChoice = InputBox("Enter the column letter to sort by (e.g., A, B, C):", "Select Column")
    ' Validate user input
    If userChoice = "" Then
        MsgBox "No column selected. Sorting cancelled.", vbExclamation, "Cancelled"
        Exit Sub
    End If
    ' Convert column letter to column number
    sortCol = UCase(userChoice)
    On Error Resume Next
    Dim colNum As Integer
    colNum = Range(sortCol & "1").Column
    On Error GoTo 0
    ' Check if the column is valid
    If colNum < 1 Or colNum > lastCol Then
        MsgBox "Invalid column selected. Please choose a valid column.", vbCritical, "Error"
        Exit Sub
    End If
    ' Ask the user for sorting order
    userOrder = InputBox("Enter sorting order: 'A' for Ascending, 'D' for Descending", "Select Sorting Order")
    ' Validate sorting order
    If UCase(userOrder) = "A" Then
        sortOrder = xlAscending
    ElseIf UCase(userOrder) = "D" Then
        sortOrder = xlDescending
    Else
        MsgBox "Invalid sorting order. Sorting cancelled.", vbCritical, "Error"
        Exit Sub
    End If
    ' Define sorting key
    Set sortKey = ws.Cells(1, colNum)
    ' Apply sorting
    rng.Sort Key1:=sortKey, Order1:=sortOrder, Header:=xlYes
    ' Confirm sorting completion
    MsgBox "Sorting completed successfully!", vbInformation, "Done"
End Sub

Detailed Explanation

1. Define Worksheet and Dataset Range

Set ws = ActiveSheet
lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
lastCol = ws.Cells(1, Columns.Count).End(xlToLeft).Column
Set rng = ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol))

• ws is set to the active worksheet.
• lastRow finds the last non-empty row in column A.
• lastCol finds the last non-empty column in row 1.
• rng defines the dataset range.

2. Ask the User for Column Selection

userChoice = InputBox("Enter the column letter to sort by (e.g., A, B, C):", "Select Column")

• The InputBox prompts the user to enter a column letter.
• If the user presses cancel, the macro exits.

sortCol = UCase(userChoice)
On Error Resume Next
Dim colNum As Integer
colNum = Range(sortCol & "1").Column
On Error GoTo 0

• Converts the column letter to uppercase for consistency.
• Retrieves the corresponding column number.

3. Validate Column Input

If colNum < 1 Or colNum > lastCol Then
    MsgBox "Invalid column selected. Please choose a valid column.", vbCritical, "Error"
    Exit Sub
End If

• Ensures that the selected column is within the dataset range.

4. Ask for Sorting Order

userOrder = InputBox("Enter sorting order: 'A' for Ascending, 'D' for Descending", "Select Sorting Order")

• Prompts the user to choose the sorting order.

If UCase(userOrder) = "A" Then
    sortOrder = xlAscending
ElseIf UCase(userOrder) = "D" Then
    sortOrder = xlDescending
Else
    MsgBox "Invalid sorting order. Sorting cancelled.", vbCritical, "Error"
    Exit Sub
End If

• Checks if the input is valid (A for ascending, D for descending).
• If invalid, the macro exits.

5. Apply Sorting

Set sortKey = ws.Cells(1, colNum)
rng.Sort Key1:=sortKey, Order1:=sortOrder, Header:=xlYes

• The sorting key is the first row of the selected column.
• Sorting is executed with the chosen order.

6. Notify Completion

MsgBox "Sorting completed successfully!", vbInformation, "Done"

• Displays a confirmation message when sorting is completed.

Features of This Code

1. Dynamic Column Selection – Users can pick any column dynamically.
2. Flexible Sorting Order – Users choose between ascending or descending.
3. Data Validation – Ensures correct input from users.
4. Handles Variable Dataset Sizes – Detects the dataset range automatically.
5. Error Handling – Prevents crashes due to invalid input.

How to Use

1. Open your Excel workbook.
2. Press ALT + F11 to open the VBA Editor.
3. Insert a new module (Insert → Module).
4. Copy and paste the above VBA code into the module.
5. Run the DynamicSort macro.
6. Enter the column letter and sorting order when prompted.