排序范围，不要在电子表格中排序 [英] Sort range without sorting it in a spreadsheet

问题描述

问题是关于在VBA中排序数据。假设我有一个范围（A1：A10），我想按升序排序。但是，我不希望在电子表格中进行任何更改（所以所有的计算都是在VBA代码中进行的）。操作的输出应该是一个 NewRange ，其中所有的数字都被排序。

Question is about sorting data in VBA. Suppose I have a Range("A1:A10") which I want to sort in ascending order. However, I do not want any changes in my spreadsheet (so all the calculations are made within a VBA code). The output of the operation should be a NewRange where all the numbers are sorted.

有人有关于这个问题的想法？

Has someone ideas about this problem?

推荐答案

这只是一个可以适应您需求的示例，它使用 B11：B20 作为 NewRange ：

This is just a sample that you may adapt to your needs, it uses B11:B20 as NewRange:

Sub SortElseWhere()
    Dim A As Range, NewRange As Range

    Set A = Range("A1:A10")
    Set NewRange = Range("B11:B20")
    A.Copy NewRange
    NewRange.Sort Key1:=NewRange(1, 1), Order1:=xlAscending, Header:=xlNo, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub

原始单元格不排序，它们是mer

The original cells are not sorted, they are merely copied to another location which is sorted.

编辑＃1：

在此版本中， NewRange 不是单元格范围，而是内部数组：

In this version, NewRange is not a range of cells, but an internal array:

Sub SortElseWhere2()
    Dim A As Range, NewRange(1 To 10) As Variant
    Dim i As Long, strng As String
    i = 1
    Set A = Range("A1:A10")
    For Each aa In A
        NewRange(i) = aa
        i = i + 1
    Next aa

    Call aSort(NewRange)

    strng = Join(NewRange, " ")
    MsgBox strng

End Sub

Public Sub aSort(ByRef InOut)

    Dim i As Long, J As Long, Low As Long
    Dim Hi As Long, Temp As Variant

    Low = LBound(InOut)
    Hi = UBound(InOut)

    J = (Hi - Low + 1) \ 2
    Do While J > 0
        For i = Low To Hi - J
          If InOut(i) > InOut(i + J) Then
            Temp = InOut(i)
            InOut(i) = InOut(i + J)
            InOut(i + J) = Temp
          End If
        Next i
        For i = Hi - J To Low Step -1
          If InOut(i) > InOut(i + J) Then
            Temp = InOut(i)
            InOut(i) = InOut(i + J)
            InOut(i + J) = Temp
          End If
        Next i
        J = J \ 2
    Loop
End Sub

这篇关于排序范围，不要在电子表格中排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！