合并行，对一列值求和，并保持最早的开始时间和最新的结束时间-第2部分 [英] Merge rows, sum one column of values, and keep earliest start time and latest end time - Part 2

问题描述

此问题对此

代码将需要识别这些内容，然后合并两行，并保持最早的开始日期&时间和最近的结束日期&时间，并将数据分别添加到最后两列中.在下面的示例中，最后一列的数据值为0.如果最后一行的顶部值是5，第二行(黄色突出显示区域)是243，则第一列的最终值将显示158，J列的最终值将显示248.

在此先感谢您的帮助.

解决方案

尝试以下代码:

  Sub Test2()昏暗的Rng作为范围，dRng作为范围昏暗的我，LR长的'lastrow随着申请.ScreenUpdating = False.EnableEvents =假.Calculation = xlCalculationManual结束于LR = Range("A"& Rows.Count).End(xlUp).Row设置Rng = Range("A2:J2")对于i = 3到LR如果Rng(1)=单元格(i，1)和Rng(2)=单元格(i，2)并且Rng(3)=单元格(i，3)_和Rng(4)=单元格(i，4)和Rng(5)=单元格(i，5)和Rng(6)=单元格(i，6)然后设置Rng = Range(Rng(1)，Cells(i，10))别的如果Rng.Rows.Count>1然后GoSub mSub设置Rng = Range(像元(i，1)，像元(i，10))万一下一个如果Rng.Rows.Count>1然后GoSub mSub如果不是dRng则为dRng.EntireRow.Delete随着申请.ScreenUpdating =真.EnableEvents = True.Calculation = xlCalculationAutomatic结束于退出子mSub:带工作表功能Rng(7)= .Min(Rng.Columns(7))Rng(8)= .Max(Rng.Columns(8))Rng(9)= .Sum(Rng.Columns(9))Rng(10)= .Sum(Rng.Columns(10))结束于如果dRng无效设置dRng = Range(Rng(2，1)，Rng(Rng.Count))别的设置dRng = Union(dRng，Range(Rng(2，1)，Rng(Rng.Count)))万一返回结束子 

This question adds additional requirements to this question.

This first screen shot shows all the columns and a sample of rows that we are working with. The data will be sorted. The sub will need to match all the data that is shown in Red text:

The code will need to identify these and then merge the two rows, keeping the earliest Start date & time and the latest End date & time, and add the data in the last two columns respectively. In the below example the data values are 0 in the last column. If there was a 5 in the top one and 243 in the second line (of the yellow highlighted area), then column I would show 158 and column J would show 248 for the final values.

Thanks in advance for your assistance.

解决方案

Try this code:

Sub Test2()

  Dim Rng As Range, dRng As Range
  Dim i As Long, LR As Long 'lastrow

    With Application
     .ScreenUpdating = False
     .EnableEvents = False
     .Calculation = xlCalculationManual
    End With

    LR = Range("A" & Rows.Count).End(xlUp).Row
    Set Rng = Range("A2:J2")

    For i = 3 To LR
     If Rng(1) = Cells(i, 1) And Rng(2) = Cells(i, 2) And Rng(3) = Cells(i, 3) _
        And Rng(4) = Cells(i, 4) And Rng(5) = Cells(i, 5) And Rng(6) = Cells(i, 6) Then

      Set Rng = Range(Rng(1), Cells(i, 10))

     Else
      If Rng.Rows.Count > 1 Then GoSub mSub
      Set Rng = Range(Cells(i, 1), Cells(i, 10))
     End If
    Next

    If Rng.Rows.Count > 1 Then GoSub mSub
    If Not dRng Is Nothing Then dRng.EntireRow.Delete

    With Application
     .ScreenUpdating = True
     .EnableEvents = True
     .Calculation = xlCalculationAutomatic
    End With

  Exit Sub

mSub:

    With WorksheetFunction
     Rng(7) = .Min(Rng.Columns(7))
     Rng(8) = .Max(Rng.Columns(8))
     Rng(9) = .Sum(Rng.Columns(9))
     Rng(10) = .Sum(Rng.Columns(10))
    End With

    If dRng Is Nothing Then
     Set dRng = Range(Rng(2, 1), Rng(Rng.Count))
    Else
     Set dRng = Union(dRng, Range(Rng(2, 1), Rng(Rng.Count)))
    End If

  Return
End Sub

这篇关于合并行，对一列值求和，并保持最早的开始时间和最新的结束时间-第2部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！