Author: Oscar Cronquist Article last updated on April 19, 2018

awall asks:

Hey, can you do the opposite of this - not random order but this is my situation.
I have 3 rows.

A1 - Item Number
B1 - Description
C1 - Price

These will constantly be having new numbers put in.

Is there a way to have it when you add a number into the A column it will automatically sort into numerical order?

Or can I create a button that after I input all my data click it and it updates all 3 rows into the numerical order based on row A

This article describes a VBA macro that makes an Excel defined Table automatically sort values based on a specific column whenever an entire row is populated with values.

First I'll show you how to convert a dataset into an Excel defined Table.

Create an Excel defined Table

  1. Select cell range A1:C9.
  2. Go to tab "Insert" on the ribbon.
  3. Click "Table" button.
  4. Click OK button.

How does it work?

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

VBA code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ACell As Range
Dim ActiveCellInTable As Boolean
Dim r As Single
Dim Value As Variant
Set ACell = Target
On Error Resume Next
ActiveCellInTable = (ACell.ListObject.Name = "Table1")
On Error GoTo 0
If ActiveCellInTable = True Then
    r = Target.Row - Target.ListObject.Range.Row + 1
    For c = 1 To ActiveSheet.ListObjects(ACell.ListObject.Name).Range.Columns.Count
        If ActiveSheet.ListObjects(ACell.ListObject.Name).Range.Cells(r, c).Value = "" Then Exit Sub
    Next c
    With ActiveSheet.ListObjects(ACell.ListObject.Name).Sort
        .SortFields.Clear
        .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. Insert a new code module.
  4. Paste code to code 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.

Download Excel *.xlsm file

Sort a table (vba).xlsm