How to Sort Data in Excel Using VBA Buttons

Sorting data in Excel manually can be time-consuming, especially when dealing with large datasets.

Using VBA, you can automate this task by adding buttons that sort data in ascending or descending order with a single click. In this guide, we will walk through how to create two buttons in Excel that sort a specific column of data, making your workflow more efficient.

How to Set Up Excel and Insert Macros Using the Developer Tab

Setting Up Excel on Your PC

1. Install Excel
   If Excel is not already installed on your computer, visit the Microsoft Excel page and follow the instructions to download and install it.
2. Open Excel
   Once installed, launch Excel by finding it in the start menu or using the desktop icon.

Enabling the Developer Tab in Excel

1. Open Excel Options
   After opening Excel, go to the File menu in the top-left corner and click Options at the bottom.
2. Customize the Ribbon
   In the Excel Options window, select Customize Ribbon from the sidebar.
3. Enable Developer Tab
   On the right side of the window, check the box next to Developer and click OK. The Developer tab should now appear in the Excel ribbon.

Inserting Macros

1. Navigate to Developer Tab
   Click on the Developer tab in the ribbon.
2. Open Visual Basic for Applications (VBA)
   In the Developer tab, click on Visual Basic. This will open the VBA editor where you can write and store your macros.
3. Insert a Module
   In the VBA editor, click Insert and then choose Module. This is where you will write your code.

Understanding the Code Line by Line

The code below sorts data in an Excel worksheet based on a specific column. It uses two buttons to sort in ascending and descending order.

Declaring Variables

We start by declaring the variables used in the code to define the data range and the column for sorting.

   Dim total_data As Range
    Dim specific_column As Range

Dim: This is used to declare variables in VBA.
total_data: This variable represents the range of cells (A to H) that we want to sort.
specific_column: This variable defines the column used as the key for sorting.

Setting the Data Range and Sorting Column

Next, we set the range of data and the specific column for sorting.

    Set total_data = Worksheets("Sheet1").Range("A:H")
    Set specific_column = Worksheets("Sheet1").Range("F:F")

Set total_data: This assigns the range of columns A to H on Sheet1 to the variable total_data.
Set specific_column: This assigns column F (the Salary column, for example) as the sorting key.

Sorting in Descending Order

This part of the code sorts the data in descending order based on the specified column.

    total_data.Sort Key1:=specific_column, Order1:=xlDescending, Header:=xlYes

total_data.Sort: This sorts the entire range of data in total_data.
Key1:=specific_column: The sorting is based on the column specified by specific_column (column F).
Order1:=xlDescending: This sorts the data in descending order.
Header:=xlYes: This ensures that the first row is treated as a header and is not sorted with the rest of the data.

Selecting the First Cell After Sorting

Finally, we select cell A1 after sorting to reset the focus.

   Worksheets("Sheet1").Cells(1, 1).Select

Cells(1, 1): This represents cell A1 on Sheet1.
.Select: This selects cell A1 to bring the focus back to the top-left of the worksheet.

Full Code for Copying

Option Explicit

Private Sub CommandButton1_Click()
    Dim total_data As Range
    Dim specific_column As Range

    Set total_data = Worksheets("Sheet1").Range("A:H")
    Set specific_column = Worksheets("Sheet1").Range("F:F")

    total_data.Sort Key1:=specific_column, Order1:=xlDescending, Header:=xlYes

    Worksheets("Sheet1").Cells(1, 1).Select
End Sub

Private Sub CommandButton2_Click()
    Dim total_data As Range
    Dim specific_column As Range

    Set total_data = Worksheets("Sheet1").Range("A:H")
    Set specific_column = Worksheets("Sheet1").Range("F:F")

    total_data.Sort Key1:=specific_column, Order1:=xlAscending, Header:=xlYes

    Worksheets("Sheet1").Cells(1, 1).Select
End Sub

Running the Code

After inserting the code into the VBA editor, insert buttons onto the worksheet and assign the macros.
Button 1 will run the macro that sorts the data in descending order, while Button 2 sorts the data in ascending order.
Click the buttons to see the results in your Excel worksheet.

Flow Chart Of the Code

Result

Before

After Running the code

Conclusion

By automating the sorting process, this VBA code helps you sort data quickly with the click of a button. Now, you can manage and organize your Excel sheets more efficiently.

Resources
For more information about using VBA in Excel, visit Microsoft’s official VBA documentation. and Pillow

if you like this blog post on How to Sort Data in Excel Using VBA Buttons , then you would love our other blog post on How to Find the Last Row and Column in Excel Using VBA