Iteration in Excel VBA

It's been a while since I have used VBA on Excel.

I want to alphabetize the contents of each column on the sheet.

This is what I have:

Range("A1").Select
    Range("A1:A19").Select
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("A1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet2").Sort
        .SetRange Range("A1:A19")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("B1").Select
End Sub

How can I make this into a for loop that keeps going as long as the range is active?

Answers


Here you go. This code assumes your data is laid out in some type of table format. Also, it assumes you want the entire column sorted (including blanks and such). If you want to make the range more specific or just set it with a hard reference adjust the code where I commented.

Sub sortRange()

Dim wks As Worksheet
Dim loopRange As Range, sortRange As Range

Set wks = Worksheets("Sheet1")

With wks

    'can change the range to be looped, but if you do, only include 1 row of the range
    Set loopRange = Intersect(.UsedRange, .UsedRange.Rows(1))

    For Each cel In loopRange

        Set sortRange = Intersect(cel.EntireColumn, .UsedRange)

        With .Sort
            .SortFields.Clear
            .SortFields.Add Key:=sortRange
            .SetRange sortRange
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With

    Next

End With

End Sub

Like this?

Option Explicit

Sub sample()
    Dim i As Long

    With Sheets("Sheet1")
        For i = 1 To .UsedRange.Columns.Count
            .Columns(i).Sort Key1:=.Cells(1, i), Order1:=xlAscending, _
            Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
            Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
        Next i
    End With
End Sub

Need Your Help

segmentation fault x86 <_dl_debug_state>

assembly gdb segmentation-fault glibc

I have some asm programs made by my own compiler, and when i want to run them, they have at the very end a segmentation fault. All instructions are executed the way i want to, but the execution fin...

log4j - logs not written to file

java logging log4j

I've seen questions about it, but none of it helped me.