spot_img

How To Sort Data In Columns With VBA Instead Of Excel Sorting

In this article we are going to learn how to sort data in columns with VBA instead of using Excel sorting. You will learn how to do this by doing an example. We are going to random sort the data in columns using VBA.

To do this follow these steps:

STEP-1

First write any 5 random names in column A. Now you are going to write 5 random numbers to column B.

STEP-2

Note: you can skip step 2 and step 3 if the developer tab is already open

Now place a command button on your Excel sheet. To open the developer tab right click anywhere on the ribbon (the top menu) and select “Customize the Ribbon” from the drop down menu.

Customize The Ribbon

STEP-3

An option dialog box will open. Click on the DEVELOPER check box (it is under “Customize the Ribbon Main Tabs”) and press ok.

Add Developer Tab

STEP-4

The developer tap is now visible and is present next to the view tab on the top menu bar. Now click on the DEVELOPER tab and select “Insert“.

Insert Controls

STEP-5

Select the Command button in the ActiveX Controls.

ActiveX controls

STEP-6

Drag the command button on your Excel worksheet. Right click on the command button (make sure Design Mode is selected) go to properties. Write Rand Sort Names in the “Caption” field

Note: you can change the color of the command button, the font, font style, font color and even the name of the command button by right clicking on it and going to properties (make sure design mode is selected)

STEP-7

A new window (Visual Basic Editor) will open which will have a dialog box in the center. You will write the code in this dialog box.

Command Button VBA code

STEP-8

Now declare four variables. One variable of type String we call tempString, one variable of type Integer you call tempInteger, one variable of type Integer you call a, and one variable of type Integer you call b.

Dim tempString As String, tempInteger As Integer, a As 
Integer,
b As Integer

After this use the worksheet function RandBetween.

For a = 1 To 5
Cells (a, 2).Value = WorksheetFunction.RandBetween(0, 1000)
Next a

STEP-9

You will use the numbers next to each name to sort the names. The name with the lowest number first, the name with the second lowest number, second, etc.

Now use a Double Loop.

For a = 1 To 5
For b = a + 1 To 5

Add the following line of code:

If Cells (b, 2).Value < Cells (a, 2).Value Then

Example: for a = 1 and b = 2, Sarah and Laura are compared. Because Sarah has a lower number, you swap Sarah and Laura. Sarah is at the first position now.

If the condition is true, swap the names.

tempString = Cells (a, 1).Value
Cells (a, 1).Value = Cells (b, 1).Value
Cells (b, 1).Value = tempString

And swap the numbers.

tempInteger = Cells (a, 2).Value
Cells (a, 2).Value = Cells(b, 2).Value
Cells (b, 2).Value = tempInteger

STEP-10

Close the IF statement

End If

STEP-11

Close both the FOR loops

Next b
Next a

STEP-12

This is how the complete code should look like

Sort Columns Code

After writing down the entire code close the window (Visual Basic Editor) by clicking on the cross(x) icon on the top right corner of the screen. Don’t worry. Excel won’t get closed.

IMPORTANT: IT IS EXTREMELY IMPORTANT THAT YOU WRITE THIS CODE BEWTEEN Private Sub and End Sub. OTHERWISE IT WON’T WORK

STEP-13 Click on the command button and see the result

Sorting Columns Sheet VBA

You have now successfully sorted data and names using VBA.

Template

You can download the Template here – Download
spot_img
Previous articleAvoid Errors Using IFERROR-Everyone Should Know
Next articleLinking Text Box To A Specific Cell