循环遍历列中的范围

I have a sheet with with orders and a timestamp.

我有一张有订单和时间戳的表格。

I want to sort these rows based on the timestamp in the column on the right, but I want to keep their grouping position kind of. Like I want the 4 first rows to be sorted but stay in the 4 first row, then I want to sort row 8 and 9, but have them stay there.

我想根据右边列中的时间戳对这些行进行排序，但我想保持它们的分组位置。就像我想要第4行被排序但是保持在第4行，然后我想要排序第8行和第9行，但是让它们保持在那里。

I have managed to select both ranges, and leave out the "middle", but the .sort method will not work unless the key cells are in one range. So I thought maybe if I loop through the different ranges in the column or something like that.

我已经成功地选择了这两个范围，并省略了“中间”，但是.sort方法不能工作，除非关键单元格在一个范围内。所以我想，如果我循环遍历列中的不同范围或者类似的东西。

Here is the code I have for now, thought I don't think it will make a difference.

这是我现在的代码，我想它不会有什么不同。

    Dim LR As Long, cell As Range, rng As Range
    With Sheets("Ark1")
    Dim start As Range
    Set start = Range("N16")

    LR = .Range("N" & Rows.Count).End(xlUp).Row
    For Each cell In .Range("N16:N" & LR)
        If cell.value <> "" Then
            If rng Is Nothing Then
                Set rng = cell
            Else
                Set rng = Union(rng, cell)
            End If
        End If
    Next cell
        rng.Sort key1:=start, order1:=xlAscending, Header:=xlGuess
    End With

2 个解决方案

#1

Well, the easiest way to achieve your goal is to re-numerate the rows in a custom order. For example you assign number #1 to the first groups of the rows, #2 to the second and so on. Then you just sort all the range by two columns, first would be your custom "order" and the second timestamp. :)

实现目标的最简单方法是按照自定义顺序重新对行进行编号。例如，您将#1分配给第一行的第一组，#2分配给第二个，以此类推。然后将所有范围按两列进行排序，首先是自定义的“order”和第二个时间戳。:)

#2

Assuming that each block is separated by blank values in column N, and that you want to sort the whole rows for each blocks, here is the code I suggest:

假设每个块被列N中的空值分隔，并且要为每个块对整个行进行排序，下面是我建议的代码:

Public Sub testSort()
    Dim firstRow As Long
    Dim lastRow As Long
    Dim blockStart As Long
    Dim blockEnd As Long
    Dim rng As Range

    firstRow = 3
    lastRow = Cells(Rows.Count, 14).End(xlUp).Row
    blockStart = firstRow

    Do
        If Cells(blockStart + 1, 14) <> "" Then
            blockEnd = Cells(blockStart, 14).End(xlDown).Row
        Else
            blockEnd = blockStart
        End If
        Set rng = Range(Rows(blockStart), Rows(blockEnd))
        rng.Sort Key1:=rng.Cells(, 14), Order1:=xlAscending, Header:=xlNo
        blockStart = Cells(blockEnd, 14).End(xlDown).Row
    Loop Until blockEnd >= lastRow

End Sub