Excel VBA can sort a selected, single-column range of numbers with a bubble-sort macro: the code below repeatedly compares neighboring values and swaps them when they are out of order. Bubble sort is useful for learning how sorting works or handling a small list in VBA, but Excel’s built-in Sort command is usually faster and easier for everyday sorting.
What bubble sort does
Bubble sort is a simple comparison-based sorting algorithm. It moves through a list, compares each pair of adjacent items, and exchanges their positions if they are in the wrong order. After a pass, one of the largest values in an ascending sort has reached the end of the unsorted section. Further passes shorten that section until no swaps are needed.
For example, take the values 5, 2, 4. The first comparison finds that 5 is greater than 2, so the pair becomes 2, 5, 4. The next comparison swaps 5 and 4, leaving 2, 4, 5. A later pass finds no out-of-order pair, so the list is sorted.
The name describes how larger values move toward their final positions over repeated passes. The method is easy to follow, which makes it a familiar programming exercise, but its repeated comparisons make it inefficient on large lists.
Bubble sort VBA code for one numeric column
This macro sorts the selected range in ascending numerical order. Select a contiguous range with at least two cells in one column; include only numeric values, with no header, blanks, or error values. The macro reads the selection into an array, sorts the array, and writes the result back into the same cells.
Option Explicit
Sub BubbleSortSelectedNumbers()
Dim target As Range
Dim values As Variant
Dim temp As Variant
Dim i As Long
Dim lastUnsorted As Long
Dim lastSwap As Long
Dim rowNum As Long
If TypeName(Selection) <> "Range" Then
MsgBox "Select one column of numeric cells first."
Exit Sub
End If
Set target = Selection
If target.Areas.Count <> 1 Or target.Columns.Count <> 1 _
Or target.Rows.Count < 2 Then
MsgBox "Select one contiguous column with at least two cells."
Exit Sub
End If
values = target.Value2
' Reject blanks, text, and cell errors before comparing values.
For rowNum = 1 To UBound(values, 1)
If IsError(values(rowNum, 1)) Then
MsgBox "The selection must contain numbers only."
Exit Sub
End If
If Not IsNumeric(values(rowNum, 1)) Then
MsgBox "The selection must contain numbers only."
Exit Sub
End If
Next rowNum
lastUnsorted = UBound(values, 1)
Do While lastUnsorted > 1
lastSwap = 0
For i = 1 To lastUnsorted - 1
If CDbl(values(i, 1)) > CDbl(values(i + 1, 1)) Then
temp = values(i, 1)
values(i, 1) = values(i + 1, 1)
values(i + 1, 1) = temp
lastSwap = i
End If
Next i
' Everything after the last swap is already in order.
lastUnsorted = lastSwap
Loop
target.Value2 = values
End Sub
The macro deliberately sorts just one column of numbers. It does not sort related rows by a key, handle text ordering, or preserve formulas in the selected cells. Those limits keep the example focused and make its behavior predictable.
How the code works
The Selection object represents the cells currently selected in Excel. The checks require a single contiguous area, one column, and at least two rows. This avoids trying to sort multiple disconnected ranges or a rectangular block with an undefined row order.
target.Value2 loads the selected cell contents into a VBA array. Working with an array avoids reading and writing worksheet cells for every comparison, which would add overhead. The validation loop then rejects cell errors and anything VBA does not recognize as numeric.
The nested behavior comes from a Do While loop around a For loop. The inner loop compares neighboring array entries. When the left entry is larger, a temporary variable holds it while the two entries exchange places. The outer loop repeats until a pass makes no swaps.
lastSwap is an optimization: it records the position of the final exchange in a pass. Values to its right are already in their final ascending order, so the next pass need not compare them again. This can reduce unnecessary comparisons, but it does not change bubble sort’s poor large-list performance.
How to add and run the macro
- Save a copy of the workbook before running the macro. VBA changes are not reversed by Excel’s Undo command (Ctrl+Z on Windows or Command+Z on Mac).
- In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. You can also use Developer > Visual Basic. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon.
- In the editor, choose Insert > Module, then paste the code into the module window.
- Return to the worksheet and select only the numeric cells to sort. Do not include a heading.
- Run the macro with Alt+F8, choose
BubbleSortSelectedNumbers, and select Run. You can also run it from the Visual Basic Editor. - Save the workbook as an Excel Macro-Enabled Workbook (
.xlsm) if you need to keep the VBA code. A standard.xlsxfile cannot store VBA macros.
On Mac, use Developer > Visual Basic to open the editor if the keyboard shortcut is unavailable; the exact function-key combination can depend on the keyboard and macOS settings. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros. Use the desktop app to run this code.
Important limits and data effects
The example writes the sorted array back with Value2. That replaces formulas in the selected cells with their current calculated values. It changes cell contents but does not intentionally change formatting. If the range contains formulas you need to keep, do not run this version on that range; sort a copy or adapt the macro to work with a separate output range.
Dates stored in Excel cells are represented internally as numbers, so this code can sort date serial values numerically. The displayed order will be chronological when the cells contain valid dates using the same date system. However, this is a numeric sorter, not a general-purpose date or mixed-data sorter. Text labels, blanks, and error values cause it to stop without sorting.
Selection size matters. Bubble sort makes up to roughly n × (n − 1) / 2 comparisons for n values in the worst case. That is about 4,950 comparisons for 100 items, but about 499,500 for 1,000 items. The array improves worksheet access time, yet the algorithm still scales quadratically. Select the actual data range rather than an entire worksheet column.
When to use Excel’s built-in sort instead
For ordinary worksheet data, Excel’s sort tools are generally the better choice. They are designed to sort larger ranges, can sort rows based on one or more columns, and offer controls such as ascending or descending order. Select the data and use Data > Sort, or use the relevant sort command in the Data tab’s Sort & Filter group. Check that Excel has identified any header row correctly before confirming a sort.
If you want a sorted result without changing the source list, Microsoft 365 and Excel 2021 also include the SORT worksheet function. With numbers in A2:A6, enter =SORT(A2:A6,1,1) in an empty cell. It returns a sorted copy in ascending order, spilling into cells below; for example, 5, 2, 4, 1, 3 returns 1, 2, 3, 4, 5. The source values remain in place. This is a worksheet formula, not a VBA macro, and it requires a version that supports dynamic arrays.
Choose the VBA bubble sort when the algorithm itself is the point, when a small demonstration is useful, or when you specifically need a simple sort routine inside a VBA project. Choose a built-in sort for routine table work, and consider SORT when you need a live sorted view that leaves the original range untouched.
Common problems and fixes
- The macro is not listed: Confirm the code is in a standard module, not a worksheet or workbook event module, and that the workbook is saved in a macro-enabled format.
- It rejects the selection: Select one contiguous column with two or more cells. Remove the heading, blank cells, text, and errors, or use a different sorting method for mixed data.
- The original formulas disappeared: The macro writes values back over the selected cells. Restore the workbook copy or undo is not available for the macro; use a separate destination range for future runs.
- The result does not match a text sort: This code compares numbers, not words. Text order depends on text-comparison rules and may also involve case, locale, and blanks; use Excel’s Sort dialog for typical mixed worksheet data.
- Excel blocks the macro: Macro security settings may prevent code from running. Only enable macros in workbooks you trust, and follow your organization’s security policy rather than lowering protections broadly.
Frequently Asked Questions
Is bubble sort built into Excel?
Excel does not provide a worksheet function named bubble sort. You can write the algorithm in VBA, as shown above, but Excel’s Data tab provides built-in sorting tools for normal worksheet tasks.
Can this macro sort from largest to smallest?
Yes. In the comparison, change the greater-than test to a less-than test: swap when the left value is smaller than the right value. That produces descending numerical order. Save a copy before changing or running macro code.
Can I sort names with this code?
No. The example validates numeric values and compares them as numbers. Sorting names requires a text comparison and decisions about case and locale; Excel’s built-in sort is a simpler option for names.
Why does the macro not work in Excel for the web?
Excel for the web does not run VBA. Open the workbook in the Excel desktop application for Windows or Mac to use the Visual Basic Editor and run the macro.
Is bubble sort stable?
This ascending version swaps values only when the left value is strictly greater than the right value. Equal numeric values are therefore not swapped past one another, so the algorithm is stable for equal values. Because this example sorts one numeric column, stability has limited practical significance unless the code is extended to move whole records.
Related
- Excel Split Cell Based on Character: Methods That Work
- Excel Split Cell at a Specific Character?
- Excel Slicers for Multiple PivotTables: How to Connect
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Count Jess in Excel VBA: A Step-by-Step GuideTo count cells whose value is Jess, run a VBA macro that checks a chosen column and counts…
- Define a Function in Excel VBA: Syntax and ExamplesTo define a function in Excel VBA, write a Function procedure in a standard VBA module, give it…
- Excel Macro Extension: Which File Type Should You Use?The standard Excel macro-enabled workbook extension is .xlsm. Use it when saving a workbook that contains VBA macros;…
- Excel VBA: Hide a Column RangeTo hide a range of columns with Excel VBA, set its Hidden property to True: use Columns("B:D").Hidden =…
- VBA Open UserForm: How to Show a Form in ExcelTo open a VBA UserForm in Excel, call its Show method: for example, frmCustomer.Show. Replace frmCustomer with the…
- LEFT Excel Macro: Formula and VBA ExplainedLEFT is not an Excel macro: it is a worksheet function that returns a specified number of characters…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.