VBA Sort Range by Column: Practical Examples and Tips

VBA & macros · Updated November 18, 2026

To sort a range by one column in Excel VBA, call the range’s Sort method and specify the key column, sort order, and whether the range has a header. For example, ws.Range("A1:D100").Sort Key1:=ws.Range("B1"), Order1:=xlAscending, Header:=xlYes sorts all four columns together by column B, A to Z, while keeping the first row as a header.

A simple VBA sort by one column

The key rule is to sort the entire rectangular data range, not just the column used as the key. If column A contains names, B contains dates, and C contains amounts, sorting only B would separate the dates from their corresponding names and amounts. Set the range to include every column whose rows must stay together, then identify the column that determines the order.

This example sorts a fixed range on the active worksheet. Change the range and key column to match your sheet:

Sub SortByColumnB()
    Dim ws As Worksheet

    Set ws = ActiveSheet

    ' Sort all records in A1:D100 by column B.
    ws.Range("A1:D100").Sort _
        Key1:=ws.Range("B1"), _
        Order1:=xlAscending, _
        Header:=xlYes
End Sub

Here, xlAscending means ascending order: text generally goes A to Z, numbers go low to high, and dates go oldest to newest. Use xlDescending for the reverse. The key range can be a cell in the column being sorted; it does not need to contain the whole column. Header:=xlYes tells Excel that the first row of the sort range contains field names, not a record to move.

How the Sort method works

Excel’s Range.Sort method applies sort settings to the range on which it is called. Its Key1 argument identifies the primary sort column, Order1 selects ascending or descending order, and Header describes the top row. The key should be inside the range being sorted. When the key is in column B, for instance, sort a range containing column B and all related data columns.

A sort rearranges complete rows within the selected range. It does not automatically know that a nearby column outside that range belongs to the same records. Check the range boundaries before running a macro, especially when a worksheet has notes, totals, or other data immediately beside the table.

The worksheet reference matters too. A bare reference such as Range("A1:D100") refers to the active sheet, which could change if the user clicks elsewhere. Qualifying ranges with ws, as in the example, makes the target sheet explicit. You can replace ActiveSheet with a named worksheet reference when the macro should always work on a particular sheet.

Choose the range and header setting carefully

A fixed range is easy to understand and works well when the data size never changes. If records are added regularly, however, a hard-coded final row can omit new records. One option for a continuous block of data is CurrentRegion, which expands from a starting cell until it encounters blank rows or columns:

Sub SortCurrentRegionByColumnB()
    Dim ws As Worksheet
    Dim data As Range

    Set ws = ActiveSheet
    Set data = ws.Range("A1").CurrentRegion

    ' Assumes the region starts at A1 and row 1 contains headers.
    data.Sort Key1:=ws.Range("B1"), _
              Order1:=xlAscending, _
              Header:=xlYes
End Sub

This is convenient when the table is one uninterrupted block and begins at A1. It may include adjacent notes or stop early if a completely blank row or column splits the data. Inspect the layout and test the detected range before relying on it.

Set Header:=xlNo if the selected range contains data only and has no header row. If Excel is told that a header exists when it does not, the first record will be held at the top. If Excel is told there is no header when one exists, the header may be sorted into the records. Use xlGuess only when automatic detection is genuinely appropriate; an explicit yes or no is usually easier to maintain.

Sort a growing data set by its last row

For a list that grows downward, find the last used row in a dependable column and build the sort range from that result. This pattern assumes headers in row 1, records in columns A through D, and no gaps in column A between records:

Sub SortGrowingListByColumnB()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim data As Range

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

    If lastRow < 2 Then Exit Sub

    Set data = ws.Range("A1:D" & lastRow)

    ' Sort the complete records by column B; row 1 is the header.
    data.Sort Key1:=ws.Range("B1"), _
              Order1:=xlAscending, _
              Header:=xlYes
End Sub

End(xlUp) starts at the bottom of column A and moves upward to the last nonempty cell. If column A can be blank in a valid record, choose a different column that is always populated, or use an Excel Table instead. The If check prevents the macro from trying to sort a header-only range as though it had records.

For a layout that grows both down and across, do not assume that a last-row calculation also finds the last column. Determine both boundaries, or use a Table whose data area expands as rows and columns are added.

Sort by more than one column

For a tie-breaker—such as sorting first by department, then by surname—use the worksheet’s Sort object and add sort fields in priority order. The first field is primary; later fields sort records that tie on the earlier fields. This example uses a header row in A1:D100, with department in B and surname in C:

Sub SortByDepartmentThenSurname()
    Dim ws As Worksheet
    Dim data As Range

    Set ws = ActiveSheet
    Set data = ws.Range("A1:D100")

    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=ws.Range("B2:B100"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        .SortFields.Add Key:=ws.Range("C2:C100"), _
            SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        .SetRange data
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

In this pattern, the sort-field keys refer to data rows rather than the header cells. The key ranges and the full sort range must describe the same records. Clear old sort fields before adding new ones; otherwise, settings left from an earlier sort may affect the result. Set .Header deliberately, and use .Orientation = xlTopToBottom for the usual row-based list.

Common sorting problems and how to avoid them

  • Only one column moves: The range passed to Sort may include only the key column. Expand it to include every field belonging to each record.
  • The header moves into the data: Check that Header:=xlYes is used for a range with headers, or set the sort object’s .Header property correctly.
  • New rows are not sorted: Replace a fixed ending row with a suitable last-row calculation or use a Table.
  • Numbers sort unexpectedly: Some values that look numeric are stored as text. Convert them to numbers consistently before sorting; a sort cannot reliably treat mixed text and numeric representations as one clean numeric sequence.
  • Dates appear out of order: Check whether dates are real Excel date values or text strings. Text dates can follow text ordering rather than chronological ordering, especially when formats differ.
  • Blank rows split the list: A blank row can interrupt CurrentRegion. Select the intended rectangle directly or use a Table.
  • Results vary between runs: Explicitly set the worksheet, range, header behavior, and sort fields instead of relying on whichever sheet or prior sort configuration is active.

Sorting is destructive in the sense that it changes row order, and running a macro cannot be reversed with Ctrl+Z. Save a copy of the workbook before running VBA, particularly when the range contains formulas, manually arranged rows, or business-critical records. A macro should also be limited to the intended worksheet and data block; avoid sorting an entire sheet unless that is explicitly what you need.

Using an Excel Table or a worksheet sort instead

If the list is a structured dataset that regularly gains records, convert it to an Excel Table with Insert > Table or Home > Format as Table. Tables expand as data is added and provide header filter buttons for sorting. VBA can refer to the Table by name and sort its data body, which can be more maintainable than repeatedly calculating row limits. When writing a Table-based macro, confirm whether the table has any data rows before referring to its data body.

For a one-time task, the ribbon may be simpler than VBA: click inside the data, then use Data > Sort to select a column, order, and header setting. Excel may prompt to expand a selection when only one column is selected. Choose the option that keeps related columns together. VBA is useful when the same sort must run repeatedly, be attached to a button, or form part of a larger workflow.

VBA macros run in the desktop versions of Excel, including Excel for Microsoft 365 on Windows and Mac, but Excel for the web does not run VBA macros. On Windows, open the Visual Basic Editor with Alt+F11 and the Macro dialog with Alt+F8. On Mac, the corresponding shortcuts are usually Option+F11 and Option+F8; some keyboards require the Fn key as well. Save a macro-enabled workbook as .xlsm if you need to keep its VBA code.

Frequently Asked Questions

How do I sort a range by column B in ascending order?

Call Sort on the full data range, set Key1 to a cell in column B, use Order1:=xlAscending, and set Header to match the range. For example, a range A1:D100 with headers can use ws.Range("A1:D100").Sort Key1:=ws.Range("B1"), Order1:=xlAscending, Header:=xlYes.

How do I sort in descending order?

Change the order argument to Order1:=xlDescending. The other settings remain the same: sort the full record range, identify the key column, and correctly specify whether the first row is a header.

Can I sort a range without a header row?

Yes. Set Header:=xlNo for a range that contains only records. If the first row is a field-name row, use xlYes so Excel keeps it out of the sorted records.

Why does my VBA sort separate values from their rows?

The macro is probably sorting only the key column or leaving related columns outside the selected range. Set the sort range to include every column in each record so Excel moves complete rows together.

Can Excel for the web run a VBA sort macro?

No. Excel for the web can open and edit many workbook contents, but it does not execute VBA macros. Run the macro in desktop Excel, or use a worksheet sort when you need to work entirely 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.