是否有一种方法来排序单元格地址?

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

对union的单元格地址进行排序

我的第一个想法是，一个联合是自动排序Excel，但我被证明是错误的。这里是一个使用快速排序的建议，来自用户"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

Answer 2

对union的单元格地址进行排序升序行和降序列

终于得到了我想要的答案!: -)

Option Explicit
Sub Split_and_Sort()
Dim myRangeStr As String
Dim myRangeArr() As String
Dim myRange As Range
Dim iCt As Integer
Dim maxCt As Integer

myRangeStr = "$E$12,$B$11:$C$12,$G$14,$F$2,$F9"
myRangeArr = Split(myRangeStr, ",")

Debug.Print vbCrLf & "ORIGINAL:"
Debug.Print myRangeStr & vbCrLf

iCt = 1
Range("A1") = "Address"
Range("B1") = "Row"
Range("C1") = "Column"

For iCt = 0 To UBound(myRangeArr)
If myRangeArr(iCt) <> "" Then
'Debug.Print iCt; " "; myRangeArr(iCt)
maxCt = iCt + 1

Range("A1").Offset(iCt + 1, 0) = myRangeArr(iCt)
Range("B1").Offset(iCt + 1, 0) = Range(myRangeArr(iCt)).Row
Range("C1").Offset(iCt + 1, 0) = Range(myRangeArr(iCt)).Column
End If
Next iCt
Call SortCurrentRegion

Set myRange = Range(Range("A2").Value)
'iCt = 1: Debug.Print: Debug.Print iCt; myRange.Address

'create sorted union
For iCt = 2 To maxCt
Set myRange = Union(myRange, Range(Range("A1").Offset(iCt, 0)))
Debug.Print iCt; myRange.Address
Next iCt

Debug.Print vbCrLf & "SORTED:"
Debug.Print myRange.Address
End Sub
Sub SortCurrentRegion()
Dim sortRange As Range
Set sortRange = ActiveSheet.Range("A1").CurrentRegion

With ActiveSheet.Sort
.SortFields.Clear

'sort "Col B" = "Row" ascending
.SortFields.Add2 Key:=Range("B1"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal

'sort "Col C" = "Column" descending
.SortFields.Add2 Key:=Range("C1"), SortOn:=xlSortOnValues, _
Order:=xlDescending, DataOption:=xlSortNormal

.SetRange Range("A1").CurrentRegion
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

这里是即时窗口的输出:

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

Answer 3

对union的单元格地址进行排序升序行和降序列

直接使用内置的Excel:

Option Explicit
Sub sort_union()
Dim myRange As Range
Dim myCell As Range
Dim iCt As Integer
Dim maxCt As Integer

On Error Resume Next
Application.DisplayAlerts = False
Sheets("RangeSort").Delete
Application.DisplayAlerts = True
On Error GoTo 0
Worksheets.Add.Name = "RangeSort"

Set myRange = Union(Range("$E$12"), Range("$B$11:$C$12"), _
Range("$G$14"), Range("$F$2"), Range("$F9"))

Debug.Print vbCrLf & "ORIGINAL:"
Debug.Print myRange.Address

iCt = 1
Range("A1") = "Address"
Range("B1") = "Row"
Range("C1") = "Column"

For Each myCell In myRange
Range("A1").Offset(iCt, 0) = myCell.Address
Range("B1").Offset(iCt, 0) = myCell.Row
Range("C1").Offset(iCt, 0) = myCell.Column
iCt = iCt + 1
Next myCell
maxCt = iCt - 1

Call SortCurrentRegion

Set myRange = Range(Range("A2").Value)
'Debug.Print iCt; myRange.Address
'create sorted union
For iCt = 2 To maxCt
Set myRange = Union(myRange, Range(Range("A1").Offset(iCt, 0)))
Debug.Print iCt; myRange.Address
Next iCt

Debug.Print vbCrLf & "SORTED:"
Debug.Print myRange.Address

'Delete Sheet "RangeSort"
'On Error Resume Next
'Application.DisplayAlerts = False
'Sheets("RangeSort").Delete
'Application.DisplayAlerts = True
'On Error GoTo 0

End Sub

Sub SortCurrentRegion()
Dim sortRange As Range
Set sortRange = ActiveSheet.Range("A1").CurrentRegion

With ActiveSheet.Sort
.SortFields.Clear

'sort "Col B" = "Row" ascending
.SortFields.Add2 Key:=Range("B1"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal

'sort "Col C" = "Column" descending
.SortFields.Add2 Key:=Range("C1"), SortOn:=xlSortOnValues, _
Order:=xlDescending, DataOption:=xlSortNormal

.SetRange Range("A1").CurrentRegion
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

这里是即时窗口的输出:

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

为什么$E$12在$B$11:$C$12前面?
在Immediate窗口的输出中，我们看到通过在联合中添加地址$C$11，范围$B$11:$C$12改变到联合的末尾!; (

ORIGINAL:
$E$12,$B$11:$C$12,$G$14,$F$2,$F$9
2 $F$2,$F$9
3 $F$2,$F$9,$C$11
4 $F$2,$F$9,$B$11:$C$11
5 $F$2,$F$9,$B$11:$C$11,$E$12
6 $F$2,$F$9,$B$11:$C$11,$E$12,$C$12
7 $F$2,$F$9,$E$12,$B$11:$C$12            
^^-- $C$11 is added to the union here
8 $F$2,$F$9,$E$12,$B$11:$C$12,$G$14
^^-- $B$11:$C$12 moved to the end of the union automatically
SORTED:
$F$2,$F$9,$E$12,$B$11:$C$12,$G$14