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 […]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
In this blog article, I will demonstrate basic file copying techniques using VBA (Visual Basic for Applications). I will also […]
Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]
This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]
In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
Today I would like to share with you these small event handler procedures that make it easier for you to […]
This article describes different ways to locate literal or hardcoded values in formulas. The image above shows the result from […]
This article demonstrates macros that save worksheets to a single pdf file. What's on this webpage Export all worksheets in […]
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 […]
This article demonstrates a formula that extracts values based on a condition and sorts the returned values based on values […]
The image above shows a table with two columns in cell range B3:C16, it contains random text values in column […]
This article demonstrates a formula that sorts cell values by their frequency, in other words, how many times a value […]
Table of Contents Sort text from two columns combined (array formula) How to create an array formula How to copy […]
This article demonstrates a macro and a formula that allows you to sort delimited data in a cell or cell […]
This article demonstrates a formula that sorts items arranged horizontally based on the adjacent numbers, every other column contains a […]
Question: How do I sort a range alphabetically using excel array formula? Answer: Cell range $B$2:$E$5 contains text values in random […]
The formula in cell B8 extracts a list sorted based on frequency. Array formula in B8: =TEXTJOIN("", TRUE, IF(MIN(IF((MAX(IF(COUNTIF($B$7:B7, $B$2:$E$5)=0, […]
Ralee asks in in this blog post: Sort values in parallel (array formula) If there is information in adjacent columns, […]
Overview The array formula in cell range C2:C6 creates a random list from the values in cell range A2:A6. Array […]
In this article, I will demonstrate two techniques for counting per row. The first example is simple and straightforward. The […]
The image above demonstrates a formula in cell D3 that sorts values based on character length, the value with the […]
The following array formula extracts duplicate values sorted from A to Z from cell range B3:B21. Excel array formula in […]
I will in this article demonstrate three different techniques to sort a data set in Excel. The first method sorts […]
This article describes a formula that sorts values arranged in a column from A to z by every other value. […]
This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]
Excel formula categories
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