Excel VBA- Sort column

By Ajeet Kumar

EXCEL VBA: TO SORT DATA IN A COLUMN

Suppose you are given numbers in a column A. To sort these data, we can use the sort feature provided in the excel. To sort we use the following steps:

Place the cursor at cell A1.

DATA >> SORT

The data will be sorted.

The same task can be done using VBA as well. The following code uses the Bubble Sort Technique to do this.

	A	B
1	19	SORTED
2	91	11
3	44	14
4	42	18
5	43	19
6	46	28
7	18	33
8	14	42
9	11	43
10	45	44
11	33	45
12	50	46
13	28	50
14		91

Sub SortAsc()

       

        Dim vardata As Variant

        Dim varTemp  As Variant

        Dim lngR As Long

        Dim lngPC As Long

       

        On Error GoTo lblER1

        Application.ScreenUpdating = False

       

        If Range("A1").Resize(Range("A1").Offset(Rows.Count - 1).End(xlUp).Row).Rows.Count < 2 Then

            GoTo lblER1

        End If

        vardata = Range("A1").Resize(Range("A1").Offset(Rows.Count - 1).End(xlUp).Row)

        lngPC = UBound(vardata) - 1

       

        For lngPC = 1 To lngPC

                For lngR = LBound(vardata) To UBound(vardata) - 1

                    SwapAscend vardata(lngR, 1), vardata(lngR + 1, 1)

                Next

        Next

       

        Range("B1") = "SORTED"

        For lngR = LBound(vardata) To UBound(vardata)

                Range("B" & lngR + 1) = vardata(lngR, 1)

        Next

       

lblER1:

If Err.Number <> 0 Then

    MsgBox "Error Number:" & Err.Number & vbCrLf & "Error Description: " & Err.Number

End If

Application.ScreenUpdating = True

End Sub

Sub SwapAscend(data1 As Variant, data2 As Variant)

        Dim varTemp As Variant

        On Error GoTo lblER3

       

                    If data1 > data2 Then

                                varTemp = data1

                                data1 = data2

                                data2 = varTemp

                    End If

lblER3:

If Err.Number <> 0 Then

    MsgBox "Error Number:" & Err.Number & vbCrLf & "Error Description: " & Err.Number

End If

Application.ScreenUpdating = True

End Sub