VBA Sort Column Ascending in Excel

VBA & macros · Updated November 18, 2026

To sort a column in ascending order with VBA, use Excel’s Range.Sort method and set Order1:=xlAscending. For example, sort A2:A100 with Range("A2:A100").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlNo; if those values belong to rows of related data, sort the entire table range by that column instead so the other fields stay with their records.

How Excel VBA sorting works

The Sort method sorts the cells in a specified range. Its Key1 argument identifies the column or range Excel should use to compare records, and Order1 sets the direction. Use the built-in constant xlAscending for ascending order and xlDescending for descending order.

A key distinction is the size of the range being sorted. If the range contains only one column, Excel reorders those cells. If it contains several columns, Excel reorders complete rows according to the key column. That second approach is usually what you want for a list of names, dates, products, or transactions because it keeps each record intact.

The Header argument tells Excel whether the first row in the sort range is a heading. Use xlYes when it is a header and xlNo when every row contains data. Specifying it is safer than relying on Excel to guess, particularly when the first data value looks like a label.

Sort a single column with a header

This macro sorts the values in A2:A100 while leaving the heading in A1 in place. Change the worksheet name and range to match your workbook. Before running any macro, save a copy of the workbook: VBA changes cannot be reversed with Ctrl+Z in Windows Excel or Command+Z in Mac Excel.

Sub SortOneColumnAscending()
    Dim ws As Worksheet
    Dim sortRange As Range

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set sortRange = ws.Range("A1:A100")

    ' Keep the heading in row 1 and sort the values below it.
    sortRange.Sort Key1:=ws.Range("A2"), Order1:=xlAscending, _
        Header:=xlYes, Orientation:=xlTopToBottom
End Sub

The sort range includes A1 because Excel needs to know the full area being sorted, but the key points to A2, the first data cell. With Header:=xlYes, Excel treats A1 as a heading rather than a value to move. If the range has no heading and all its cells should be sorted, use Header:=xlNo and make the first cell of the range the key.

Sort complete rows by one column

When a column is part of a table of records, include all the columns in the range. This example sorts A1:D100 by the values in column B, in ascending order, while preserving the relationship between each row’s fields.

Sub SortRowsByColumnBAscending()
    Dim ws As Worksheet
    Dim sortRange As Range

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set sortRange = ws.Range("A1:D100")

    ' Sort every field in each record by the value in column B.
    sortRange.Sort Key1:=ws.Range("B2"), Order1:=xlAscending, _
        Header:=xlYes, Orientation:=xlTopToBottom
End Sub

For example, suppose columns A through D contain an order number, customer, delivery date, and amount. Sorting only column C would move dates without moving their corresponding order numbers, customers, and amounts. Sorting A1:D100 by B2 moves whole rows and keeps those values together. Make sure the sort range covers every field in the records; omitting a column can leave that data out of the sort.

Find the last row instead of hard-coding a range

A fixed endpoint such as row 100 is suitable only if the data always ends there. For a list that grows, VBA can find the last non-empty cell in the key column and build a range from the header to that row. This example assumes the heading is in A1 and the data is in column A.

Sub SortColumnToLastRowAscending()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim sortRange As Range

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Stop if the column contains only its heading or is empty.
    If lastRow < 2 Then Exit Sub

    Set sortRange = ws.Range("A1:A" & lastRow)
    sortRange.Sort Key1:=ws.Range("A2"), Order1:=xlAscending, _
        Header:=xlYes, Orientation:=xlTopToBottom
End Sub

The End(xlUp) pattern starts at the bottom of worksheet column A and moves upward to the last non-empty cell. It works well for a continuous list, but it identifies the last used cell in that column—not necessarily the last record across every column. If column A can be blank in the final records, find the endpoint using a column that is consistently filled, or use an Excel Table that expands with new records.

Choose the right range and header setting

Before running a sort, decide whether you are sorting independent values or complete records. Then set the range and header flag to match the sheet. These are the main choices:

  • One column, no heading: Sort only the values, and set Header:=xlNo.
  • One column with a heading: Include the heading in the sort range and set Header:=xlYes.
  • Several related columns: Include the full record range and use the relevant column as Key1.
  • Data stored in an Excel Table: Prefer sorting the table so its records and table structure are handled together.

In desktop Excel for Microsoft 365 on Windows, a manual equivalent is available from Data > Sort A to Z for a selected column, or from Data > Sort when you need to choose a column and specify that the data has headers. Excel may ask whether to expand a selection if you select only one column from a larger list. Expanding the selection keeps rows together; sorting the current selection alone can separate related values.

Sort an Excel Table

Excel Tables, created with Insert > Table or Home > Format as Table, are useful for lists that change size. A table has named columns, a header row, and an automatically expanding data area. In VBA, sorting by a table column can be more maintainable than referring to a fixed address such as B2:B100, especially when the table gains rows.

A table’s sort controls are available through its ListObject.Sort object. A typical VBA approach is to identify the table, clear any old sort fields, add the desired table column as a sort field with Order:=xlAscending, and apply the sort. Use the table’s actual name and column heading in code, and check that the table contains data before referring to its data-body range. If you only need to sort a small, stable range, the simpler Range.Sort examples above are often easier to understand and maintain.

What ascending order means for different values

Ascending is not limited to numbers. Excel sorts numbers from smaller to larger, dates from earlier to later, and text alphabetically according to Excel’s sorting rules. A value that looks like a number but is stored as text may sort differently from a true number. For instance, text values such as “1”, “10”, and “2” can be ordered alphabetically rather than numerically. If a result looks unexpected, check the underlying cell types and number formats rather than assuming the sort direction is wrong.

Blank cells, formulas, and mixed data types can also affect what readers see. A formula cell is sorted using its calculated value, while the formula itself moves with its cell when you sort a complete range. A range with inconsistent blanks or text mixed into numeric data may not produce the order someone expects. Review a copy of the data first when the column contains different kinds of entries or special business rules.

By default, this basic sort is not a case-sensitive text sort. If capitalization, custom lists, or multiple sort levels matter, use Excel’s more detailed sort options or the VBA Sort object and configure the relevant settings. A single Key1 is enough for a straightforward ascending sort, but it does not describe a multi-level priority such as sorting first by department and then by surname.

Common problems and how to avoid them

  • Only one field moves: The macro’s sort range probably covers just the key column. Expand it to include all columns belonging to each record.
  • The heading is sorted into the data: Check that Header:=xlYes is used and that the range actually begins with the heading row.
  • The first record is skipped or included incorrectly: Confirm that Key1 points to a cell in the sort-key column and that the range and header assumptions agree.
  • Numbers appear in an odd order: Check whether some entries are stored as text, or whether the column contains a mix of numbers and text.
  • New rows are not sorted: Replace a fixed range with a last-row calculation or use a table that expands as data is added.
  • The macro cannot find the sheet: The name in Worksheets("Sheet1") must match the worksheet tab name exactly. A missing or misspelled name causes a runtime error.

Also check for merged cells and unusual layouts before sorting. Merged cells across a sort area can prevent a normal sort or make the result unsuitable. Filters and hidden rows deserve attention too: a sort rearranges the data range, but it does not mean that only the currently visible records are being sorted. Test the macro on a copy when the worksheet has special formatting, formulas, or hidden information.

VBA, a manual sort, or a SORT formula?

VBA is useful when sorting is part of a repeatable workflow: for example, a button can sort a report after new data is pasted. A manual sort is often safer for a one-time task because Excel’s interface makes the chosen column and sort direction visible. A dynamic-array formula can be useful when you want a sorted copy that updates as its source changes, rather than rearranging the original records.

In Microsoft 365 for Windows, the SORT worksheet function can return a sorted array, but it does not replace VBA in every situation: it displays a separate result and does not physically reorder the source cells. Use a range sort when the original data should be rearranged; use a formula when a separate, automatically updated view is preferable. Keep the full records together with either method when the columns describe the same rows.

VBA is available in desktop Excel for Windows and Mac, although some Excel features and automation capabilities differ between platforms. Excel for the web can open a macro-enabled workbook, but it cannot run or edit VBA macros. If other people use the workbook in a browser, provide a manual sorting method or another workflow they can use there.

Frequently Asked Questions

What is the VBA command to sort ascending?
Use the Range.Sort method with Order1:=xlAscending. For a single-column range without a heading, for example, set the range to the cells to sort, use its first cell as Key1, and specify Header:=xlNo.

How do I sort a column but keep the rows together?
Set the sort range to include every column in the records, then set Key1 to a cell in the column that determines the order. Sorting only the key column can detach its values from the rest of each row.

How do I keep the header at the top?
Include the header row in the sort range and specify Header:=xlYes. Point the key at the first data cell in the sort column so the heading is not mistaken for a value.

Can VBA sort a column automatically when new rows are added?
Yes. You can calculate the last used row in a reliable key column before setting the range, as in the dynamic-range example. For expanding lists, an Excel Table is another practical option because it grows as records are added.

Can I undo a VBA sort with Ctrl+Z?
No. Running a VBA macro clears Excel’s normal undo history, so Ctrl+Z in Windows—or Command+Z on a Mac—will not reverse the macro’s sort. Save a copy first if the original order may need to be restored.

Will the macro run in Excel for the web?
No. Excel for the web does not run VBA macros. Use desktop Excel for Windows or Mac to run the macro, or sort manually in the browser.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.