Basics of VBA / Macros Chapter – 31 (Loop Part‑8)

⚡ Macro Developer Toolkit

Copy or Export this Code to VBA

Use these verified developer utilities to run this lesson directly inside your Microsoft Excel Visual Basic Editor.

🖇 View Code Example
Basics of VBA / Macros Chapter – 31 (Loop Part-8)
⏱ 2 min readUpdated 12 November 2026

Randomly Sort Data

Below we will look at a program in Excel VBA that randomly sorts data (in this example randomly sorts names).

Situation:

Randomly Sort Data in Excel VBA

We will declare four variables One variable of type String we call TempString, one variable of type Integer we call TempInteger, one variable of type Integer we call i, and one variable of type Integer we call j.

Dim tempString As String, tempInteger As Integer, i As Integer, j As Integer

Write 5 random numbers to column B (one for each name). We use the worksheet function RandBetween for this.

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

Result so far:

Result so far

We 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.

start a Double Loop.

For i = 1 To 5
    For j = i + 1 To 5

Add the following code line:

Advertisement
If Cells(j, 2).Value < Cells(i, 2).Value Then

For i = 1 and j = 2, Wendy and Richard are compared. Because Richard has a lower number, we swap Wendy and Richard. Richard is at the first position now. For i = 1 and j = 3, Richard and Joost are compared. Joost has a higher number so nothing happens. This way, Excel VBA gets the name with the lowest number at the first position. For i = 2, Excel VBA gets the name with the second lowest number at the second position, etc.

If true, we swap the names.

tempString = Cells(i, 1).Value
Cells(i, 1).Value = Cells(j, 1).Value
Cells(j, 1).Value = tempString

And we swap the numbers.

tempInteger = Cells(i, 2).Value
Cells(i, 2).Value = Cells(j, 2).Value
Cells(j, 2).Value = tempInteger

Don’t forget to close the If statement.

End If

Don’t forget to close the two loops.

Next j
 Next i

Test the program.Β 

Advertisement

Result:

Randomly Sort Data Result

We can add a line which deletes the numbers in column B. It’s even nicer to place the numbers of each name in an array, so no numbers are placed on your worksheet. However, for illustrative purpose we’ve chosen to place the values on the sheet.

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free Β· AI can be wrong