Excel VBA：按最后一列对二维数组results(lcol,4)进行字母排序

Got it, let's get your results array sorted by its 4th column alphabetically. I'll walk you through two reliable methods—one that works directly on the array, and another that uses a temporary worksheet for larger datasets.

Method 1: Bubble Sort (Direct Array Manipulation) #

This is great for small to medium-sized arrays since it doesn't require interacting with worksheets. It compares the 4th column values and swaps entire rows when needed.

First, make sure your array is populated correctly (I'll include your existing code for context), then add the sorting logic:

' Your existing array population code
Dim lcol As Long, i As Long
' Set lcol to your desired row count first (e.g., lcol = ThisWorkbook.Sheets(2).Cells(1, Columns.Count).End(xlToLeft).Column - 1)
ReDim results(lcol, 4) ' 1-based array: rows 1 to lcol, columns 1 to 4

For i = 1 To lcol
    results(i, 1) = ThisWorkbook.Sheets(2).Range("B1").Offset(, i - 1).Value
    results(i, 2) = "0"
    results(i, 3) = ThisWorkbook.Sheets(3).Range("C2").Offset(i - 1, 0).Value
    ' Ensure you fill results(i,4) here with your intended value
Next i

' --- Sorting Logic Starts Here ---
Dim j As Long, k As Long
Dim temp As Variant

For i = 1 To lcol - 1
    For j = i + 1 To lcol
        ' Use UCase for case-insensitive sorting; remove it if you need case-sensitive
        If UCase(results(i, 4)) > UCase(results(j, 4)) Then
            ' Swap all 4 columns of the two rows
            For k = 1 To 4
                temp = results(i, k)
                results(i, k) = results(j, k)
                results(j, k) = temp
            Next k
        End If
    Next j
Next i

Notes: #

• The UCase function ensures sorting isn't case-sensitive (e.g., "Apple" and "apple" are treated the same). Remove it if you need strict case-based ordering.
• This assumes your array is 1-based (matches your population code where you loop from i=1 to lcol). If you're using a 0-based array, adjust the loop starts/ends to 0 and lcol-1.

Method 2: Temporary Worksheet Sort (Better for Large Arrays) #

For larger datasets, Excel's built-in Sort method is more efficient. We'll write the array to a temporary sheet, sort it, then read the sorted data back into the array.

' Your existing array population code (same as above)
' ...

' --- Sorting Logic Starts Here ---
Dim wsTemp As Worksheet
Set wsTemp = ThisWorkbook.Worksheets.Add

' Write array to temporary worksheet
wsTemp.Range("A1").Resize(lcol, 4).Value = results

' Sort the range by column D (4th column) in ascending alphabetical order
wsTemp.Range("A1:D" & lcol).Sort _
    Key1:=wsTemp.Range("D1"), _
    Order1:=xlAscending, _
    Header:=xlNo ' Use xlYes if your array has headers

' Read sorted data back into the results array
results = wsTemp.Range("A1").Resize(lcol, 4).Value

' Clean up: delete the temporary sheet
Application.DisplayAlerts = False
wsTemp.Delete
Application.DisplayAlerts = True

Notes: #

• This method leverages Excel's optimized sorting algorithm, which is faster for large arrays.
• We disable DisplayAlerts to avoid the confirmation prompt when deleting the temporary sheet.

Just pick the method that fits your dataset size, and make sure the 4th column of your results array is fully populated before sorting!

内容的提问来源于stack exchange，提问作者Mohammed Zegui