VBA to Sort Column in Excel (4 Methods)

To sort in Excel with VBA, you need to apply the Range.Sort method. In this article, we will show you how to sort the column in Excel with the Range.Sort method of VBA.


Download Workbook

You can download the free practice Excel workbook from here.


Range.Sort Method in Excel VBA

Range.Sort method in VBA sorts a range of values in Excel. Here Range is an object variable that specifies the range of cells that we want to sort in ascending or descending order.

Below are the parameters that you need to know about while working with this method.

Parameter Required/ Optional Data Type Description
Key Optional Variant Specifies the range or the column whose values are to be sorted.
Order Optional XlSortOrder Specifies the order in which the sorting will be performed.
  • xlAscending = To sort in ascending order.
  • xlDescending = To sort in descending order.
Header Optional XlYesNoGuess Specifies whether the first row contains headers or not.
  • xlNo = When the column doesn’t have any headers; Default value.
  • xlYes = When the columns have headers.
  • xlGuess = To let Excel determine the headers.

4 Methods in Implementing VBA to Sort Column in Excel

In this section, you will know how to sort a single column with and without a header, multiple columns with and without headers and how to sort just by double-clicking the header in a column in Excel.

1. Embed VBA to Sort a Single Column without Header in Excel

If you want to sort a single column in your Excel worksheet with VBA code then follow the steps below.

This is our column that we will sort with VBA code.

Steps:

  • Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

  • In the pop-up code window, from the menu bar, click Insert -> Module.

  • Copy the following code and paste it into the code window.
Sub SortSingleColumnWithoutHeader()
Range("B5:B15").Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlNo
End Sub

Your code is now ready to run.

Here,

  • Key1:=Range(“B5”) → Specified B5 to let the code know which column to sort.
  • Order1:=xlAscending → Specified the order as xlAscending to sort the column in ascending order. If you want to sort the column in descending order then write xlDescending instead.
  • Header:= xlNo → As our column doesn’t have any header so we specified it with the xlNo option.

Embed VBA to Sort a Single Column without Header in Excel

  • Press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

You will see that your column is now sorted in ascending order.

Result of Embed VBA to Sort a Single Column without Header in Excel

Notice that here we have defined the data range manually as Range(“B5:B15”).

If you want to change data by adding or deleting values, you can implement the following code which automatically updates based on the cells in the dataset.

Sub SortSingleColumnWithoutHeader()
Range("B5", Range("B5").End(xlDown)).Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlNo
End Sub

Notice that instead of defining range manually by Range(“B5:B15”), we have written, Range(“B5”, Range(“B5”).End(xlDown)).

This will sort the column based on the last consecutively filled cell in it. If there are blank cells, the data will only be considered up to the first blank cell.

Read More: VBA to Sort Table in Excel (4 Methods)


2. Insert VBA Macro to Sort a Single Column with Header

In the previous section, we had a dataset of a single column with no header, but now we have a column with a header.

This time we will learn how to sort it with VBA macro.

Steps:

  • Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • In the code window, copy the following code and paste it.
Sub SortSingleColumnWithHeader()
Range("B5:B16").Sort Key1:=Range("B5"), Order1:=xlDescending, Header:=xlYes
End Sub

Your code is now ready to run.

Here,

  • Key1:=Range(“B5”) → Specified B5 to let the code know which column to sort.
  • Order1:=xlDescending → This time we will sort the column in descending order so specified the order as xlDescending.
  • Header:= xlYes → As our column has a header this time so we specified it with the xlYes option.

Insert VBA Macro to Sort a Single Column with Header

  • Run this code and you will get the column with header sorted in descending order.

Result of Insert VBA Macro to Sort a Single Column with Header

Read More: How to Sort ListBox with VBA in Excel (A Complete Guide)


Similar Readings:


3. VBA Macro to Sort Multiple Columns with or without Header

You can also sort multiple columns in your dataset with VBA code.

Steps:

  • As previously shown, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
  • In the code window, copy the following code and paste it.
Sub SortMultipleColumnsWithHeaders()
With ActiveSheet.Sort
     .SortFields.Add Key:=Range("B4"), Order:=xlAscending
     .SortFields.Add Key:=Range("C4"), Order:=xlAscending
     .SetRange Range("B4:D15")
     .Header = xlYes
     .Apply
End With
End Sub

Your code is now ready to run.

Here,

.SortFields.Add Key:=Range(“B4”), Order:=xlAscending

.SortFields.Add Key:=Range(“C4”), Order:=xlAscending

By these two lines, we are defining Cell B4 and C4 to sort the two columns associated with them in ascending order.

As we have headers in our dataset so we specified Header = xlYes, otherwise we would have written Header = xlNo inside the code.

VBA Macro to Sort Multiple Column with or without Header

  • Run this code and you will get the columns with header sorted in ascending order.

Result of VBA Macro to Sort Multiple Column with or without Header


4. Macro to Sort Data by Double Clicking on Header in Excel

If you want to sort the data with ease just by double-clicking on the header, you can do that with VBA code.

Steps:

  • Right-click on the sheet tab.
  • From the appeared option list, click View Code.
  • The code window will appear, copy the following code and paste it in there.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim iRange As Range
    Dim iCount As Integer
    iCount = Range("B4:D15").Columns.Count
    Cancel = False
    If Target.Row = 4 And Target.Column <= iCount Then
    Cancel = True
    Set iRange = Range(Target.Address)
    Range("B4:D15").Sort Key1:=iRange, Header:=xlYes
    End If
End Sub
  • Save the code.

VBA Macro to Sort Column by Double Clicking on Header in Excel

  • Now go back to the worksheet of interest and if you double click on the headers you will see the columns are reorganizing.

Result of VBA Macro to Sort Column by Double Clicking on Header in Excel

Read More: How to Sort and Filter Data in Excel (A Complete Guideline)


Things to Remember

  • You can create a named range and use it instead when you pass a range of cell references inside the Sort method. For example, if you want to sort range A1:A10, instead of passing it every time inside the code, you can create a named range of it, such as “SortRange” and use it with the Range.Sort method like Range(“SortRange”).
  • If you are not sure whether your dataset has headers or not, you can let the system determine it by using the xlGuess parameter.

Conclusion

This article showed you how to sort the column in Excel VBA. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.


Related Articles

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

4 Comments
  1. Thank you for sharing this. These few code snippets helped me improve a working task list shared by my team. I am grateful! Here is my modified code to sort by header a wider list with an unknown amount of rows.

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim iRange As Range
    Dim iCount As Integer
    iCount = Range(“A2”, Range(“I2”).End(xlDown)).Columns.Count
    Cancel = False
    If Target.Row = 1 And Target.Column <= iCount Then
    Cancel = True
    Set iRange = Range(Target.Address)
    Range("A2", Range("I2").End(xlDown)).Sort Key1:=iRange, Header:=xlNo
    End If
    End Sub

  2. Hi is there anyway to sort columns in descending order?? I tried to change the Ascending word to Descending but it doesn’t seem to work

    • Reply
      Naimul Hasan Arif Aug 30, 2022 at 12:27 PM

      Yeah. There are ways to sort columns in descending order.
      You can apply the following VBA in the dataset used in the first method to sort the data in descending order.

      Sub SortSingleColumnWithoutHeader()
      Range(“B5”, Range(“B5”).End(xlDown)).Sort Key1:=Range(“B5”), Order1:=xlDescending, Header:=xlNo
      End Sub

Leave a reply

ExcelDemy
Logo