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 coloum 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 numberical order based on row A

Answer:

Create a table

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

Sort values

Automatically sort values (vba)

The macro below sorts a table automatically (column A) when all cells in the current row have a value. See gif animation.

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

Download excel *.xlsm file

Sort a table (vba).xlsm

Related posts:

Excel vba: Filter a table using the selection change event

Select a cell in a table and the chart updates automatically (vba)

Copy table design settings

Copy excel table filter criteria (vba)

Quickly filter a column in an excel table