Excel VBA，捕获第一个“开始"消息价值和最后的“结束"；每组价值 [英] Excel VBA, capture first "start" value and last "end" value per group

问题描述

使用Excel VBA，我试图按组捕获开始"列中的第一个值和结束"列中的最后一个值.数据已经排序.示例:

Using Excel VBA, I'm trying to capture the first value in a column "Start" and the last value in a column "End", per group. Data is already sorted. Example:

我想捕获每个公司的Start_open的第一个值和Start_end的最后一个值.因此，对于公司A，代码应将B2放入Start_Open，并将C5放入Start_end.

I want to capture the first value for Start_open and the last value for Start_end per company. So for Company A code should put B2 in Start_Open and put C5 in Start_end.

使用以下代码捕获最后一个值可以正常工作:

Capturing the last value works fine using this code:

Sub First_last()

Dim i, j As Integer
Dim LastRow, LastCol As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column


For i = 2 To LastRow
    If Cells(i + 1, "A").Value <> Cells(i, "A").Value Then
        MsgBox i
        Cells(j + 2, "E").Value = Cells(i, "C").Value
        j = j + 1
    End If
Next

End Sub

我正在努力的是捕获每个组的Start_open.我认为我需要使用上述条件并使用一个计数器来捕获每个组的Start_open，但是我找不到正确的代码.请指教，谢谢！

What I'm struggling with is capturing Start_open per group. I think I need to use above condition and use a counter to capture Start_open per group but I can't find the right code. Please advise, thanks!

推荐答案

这将满足您的要求:

Sub First_Last()
    With ActiveSheet
        Dim LastRow As Long
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

        Dim numUnique
        numUnique = .Evaluate("SUM(1/COUNTIF(A:A,A2:A" & LastRow & "))")

        Dim outarr As Variant
        ReDim outarr(1 To numUnique, 1 To 2)

        Dim clmc As Variant
        clmc = .Range(.Cells(1, 3), .Cells(LastRow, 3)).Value

        Dim clmb As Variant
        clmb = .Range(.Cells(1, 2), .Cells(LastRow, 2)).Value

        Dim j As Long
        j = 1

        Dim i As Long
        For i = 2 To LastRow
            outarr(j, 1) = clmb(i, 1)
            Dim k As Long
            k = .Evaluate("AGGREGATE(14,6,ROW(A2:A" & LastRow & ")/(A2:A" & LastRow & " = " & .Cells(i, 1).Address & "),1)")
            outarr(j, 2) = clmc(k, 1)
            j = j + 1
            i = k
        Next i

        .Range("D2").Resize(UBound(outarr, 1), UBound(outarr, 2)).Value = outarr
    End With
End Sub

这篇关于Excel VBA，捕获第一个“开始"消息价值和最后的“结束"；每组价值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！