This article demonstrates how to sort a specific column in an Excel defined Table based on event code. The event macro is triggered when a record is entered and sorts the first column from small to large or A to Z.

First I'll show you how to convert a dataset into an Excel defined table. There are several things that make it easier to work with the data if you convert it to an Excel defined Table.

Create an Excel defined Table

1. Select any cell in your dataset.
2. Go to tab "Insert" on the ribbon.
3. Press with left mouse button on "Table" button.
   This dialog box appears:
   
4. Press with left mouse button on OK button.

How does it work?

The event macro below sorts an Excel defined Table automatically (column A) when all cells on the same row contain a value. The animated picture above explains it all.

VBA code

'Event code name
Private Sub Worksheet_Change(ByVal Target As Range)

'Dimension variables and declare data types
Dim ACell As Range
Dim ActiveCellInTable As Boolean
Dim r As Single
Dim Value As Variant

'Save changed cell to object variable ACell
Set ACell = Target

'Enable error handling
On Error Resume Next

'
ActiveCellInTable = (ACell.ListObject.Name = "Table1")

'Disable error handling
On Error GoTo 0

'Make sure that Excel table named Table1 exists in workbook 
If ActiveCellInTable = True Then

    'Subtract the relative row number of the Excel defined Table with the row number of changed cell and save the result to variable r
    r = Target.Row - Target.ListObject.Range.Row + 1

    'Iterate through each column in Excel defined Table
    For c = 1 To ActiveSheet.ListObjects(ACell.ListObject.Name).Range.Columns.Count

        'Check ff cell is empty and stop macro if so
        If ActiveSheet.ListObjects(ACell.ListObject.Name).Range.Cells(r, c).Value = "" Then Exit Sub

    'Continue with next column
    Next c

    'The With ... End With statement allows you to write shorter code by referring to an object only once instead of using it with each property.
    With ActiveSheet.ListObjects(ACell.ListObject.Name).Sort

        'Clear previous sorting criteria
        .SortFields.Clear

        'Add a new sort with an ascending order
        .SortFields.Add _
            Key:=Range("Table1[[#All],[Item number]]"), SortOn:=xlSortOnValues, Order _
            :=xlAscending, DataOption:=xlSortNormal
        .Apply
    End With
End If
End Sub

Where to copy the code?

1. Copy macro above.
2. Press Alt+F11 to open the VBE (Visual Basic Editor).
3. Doublepress with left mouse button on a worksheet in the Project Explorer to open the worksheet module.
4. Paste code to worksheet module.
5. Return to Excel.
6. Save your workbook as a macro-enabled workbook (*.xlsm)

How to customize macro to your workbook

Change this table name Table1 to your specific table name, in the macro above.

Change also this Table1[[#All],[Item number]] to whatever table name and header name you want to sort on.

Get the Excel file

Sort-a-table-vba.xlsm

Macro category

Save invoice data [VBA]
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]

Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]

Split data across multiple sheets [VBA]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]

Sort values category

Sort a column alphabetically
Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]

Extract a unique distinct list sorted from A to Z ignore blanks
This article demonstrates ways to extract unique distinct sorted from A to Z ignoring blanks, and based on a condition. […]

Sort dates within a date range
Array formula in D5: =SMALL(IF(($B$3:$B$12<=$E$3)*($B$3:$B$12>=$E$2), $B$3:$B$12, "A"), ROWS($A$1:A1)) How to create an array formula Copy array formula Select cell D5 […]

Excel categories

Comments (7)