Sort values in an Excel table programmatically [VBA]
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.
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 press with left mouse button on it and it updates all 3 rows into the numerical order based on row A
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
- Select any cell in your dataset.
- Go to tab "Insert" on the ribbon.
- Press with left mouse button on "Table" button.
This dialog box appears:
- 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?
- Copy macro above.
- Press Alt+F11 to open the VBE (Visual Basic Editor).
- Doublepress with left mouse button on a worksheet in the Project Explorer to open the worksheet module.
- Paste code to worksheet module.
- Return to Excel.
- 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.
Macro category
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
Sort values category
Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]
This article demonstrates ways to extract unique distinct sorted from A to Z ignoring blanks, and based on a condition. […]
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
7 Responses to “Sort values in an Excel table programmatically [VBA]”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
I believe you can replace these 5 lines from your code...
with this single line of code...
Rick Rothstein (MVP - Excel),
I appreciate your comments, great value!
Where in this code do I modify if I want to sort by a different column then the first one?
Wen,
Replace this Table1[[#All],[Item number]] with your table name and column
How do you sort from Largest to Smallest using your code?
In the long .SortFields.Add line of code toward the bottom of the procedure... try changing the xlAscending to xlDescending.
Hi, how would you be abl eto sort by other factors instead of ascending/descending; such as high/medium/low