excel - 有没有办法对单元格地址进行排序？

Question

有没有办法从左上角到右下角对单元格地址进行排序？
例如
将 targetAddress 调暗为字符串
targetAddress = "$E$12,$B$11:$C$12,$G$14,$F$2,$F9"'随机选择的单元格
我想从左上角到右下角对 targetAddress 进行排序，如下所示:
“$F$2,$F$9,$B$11:$C$12,$E$12,$G$14”

Answer 1

对联合的单元格地址进行排序
我的第一个想法是 Excel 会自动对 union 进行排序，但事实证明我错了。这是来自用户“jorge-ferreira”的使用快速排序的建议。
重新阅读问题后，我发现以下内容
解决方案不是搜索的答案。 ;-(

Option Explicit

Sub sort_union()

    Dim myRange As Range
    Dim myCell As Range
    Dim myArray(100) As String
    Dim iCt As Integer
    Dim maxCt As Long
    
    Set myRange = Union(Range("$E$12"), Range("$B$11:$C$12"), Range("$G$14"), Range("$F$2"), Range("$F9"))
    
    Debug.Print myRange.Address
    
    iCt = 0
    Debug.Print vbCrLf & "ORIGINAL:"
    For Each myCell In myRange
        myArray(iCt) = myCell.Address
        Debug.Print iCt & " : " & myCell.Address & " =========> " & myArray(iCt)
        iCt = iCt + 1
    Next myCell
    maxCt = iCt - 1
    
    Call QuickSort(myArray, 0, maxCt)

    Set myRange = Nothing

    Debug.Print vbCrLf & "SORTED:"
    Set myRange = Range(myArray(0))
    Debug.Print 0, myArray(0)
    For iCt = 1 To maxCt
        Set myRange = Union(myRange, Range(myArray(iCt)))
        Debug.Print iCt, myArray(iCt)
    Next iCt

    Debug.Print vbCrLf & myRange.Address
End Sub

'using quicksort from
'https://stackoverflow.com/questions/152319/vba-array-sort-function

Public Sub QuickSort(vArray As Variant, inLow As Long, inHi As Long)
  Dim pivot   As Variant
  Dim tmpSwap As Variant
  Dim tmpLow  As Long
  Dim tmpHi   As Long

  tmpLow = inLow
  tmpHi = inHi

  pivot = vArray((inLow + inHi) \ 2)

  While (tmpLow <= tmpHi)
     While (vArray(tmpLow) < pivot And tmpLow < inHi)
        tmpLow = tmpLow + 1
     Wend

     While (pivot < vArray(tmpHi) And tmpHi > inLow)
        tmpHi = tmpHi - 1
     Wend

     If (tmpLow <= tmpHi) Then
        tmpSwap = vArray(tmpLow)
        vArray(tmpLow) = vArray(tmpHi)
        vArray(tmpHi) = tmpSwap
        tmpLow = tmpLow + 1
        tmpHi = tmpHi - 1
     End If
  Wend

  If (inLow < tmpHi) Then QuickSort vArray, inLow, tmpHi
  If (tmpLow < inHi) Then QuickSort vArray, tmpLow, inHi
End Sub

这里是即时窗口的输出:

$E$12,$B$11:$C$12,$G$14,$F$2,$F$9

ORIGINAL:
0 : $E$12 =========> $E$12
1 : $B$11 =========> $B$11
2 : $C$11 =========> $C$11
3 : $B$12 =========> $B$12
4 : $C$12 =========> $C$12
5 : $G$14 =========> $G$14
6 : $F$2 =========> $F$2
7 : $F$9 =========> $F$9

SORTED:
 0            $B$11
 1            $B$12
 2            $C$11
 3            $C$12
 4            $E$12
 5            $F$2
 6            $F$9
 7            $G$14

$B$11:$C$12,$E$12,$F$2,$F$9,$G$14