View Single Post
  #4   Report Post  
Old September 5th 19, 05:22 PM posted to microsoft.public.excel.programming
Claus Busch Claus Busch is offline
external usenet poster
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,713
Default Reporting the maximum date within rows with the same keydata

Hi Johan,

Am Wed, 4 Sep 2019 21:17:38 -0700 (PDT) schrieb JS SL:

Works oke for a few thousands records, but....... my sheet is 500.000 records and it needs a very veeeeeeeeeeery long time to get the results of the calculation. (approx 1 minute for 1% calculation result).
Perhaps, if possible, another solution that covers also the amount of records and gives with a bit more speed the results.


Sub Test()
Dim LRow As Long, i As Long, First As Long, Cnt As Long
Dim varData As Variant, varTmp As Variant
Dim myDic As Object
Dim rngD As Range, rngO As Range
Dim myMax As Date

Set myDic = CreateObject("Scripting.Dictionary")
With ActiveSheet
LRow = .Cells(.Rows.Count, "H").End(xlUp).Row
varData = .Range("H2:H" & LRow)
For i = LBound(varData) To UBound(varData)
myDic(varData(i, 1)) = varData(i, 1)
varTmp = myDic.items
For i = LBound(varTmp) To UBound(varTmp)
First = Application.Match(varTmp(i), .Range("H:H"), 0)
Cnt = Application.CountIf(.Range("H:H"), varTmp(i))
Set rngD = .Cells(First, "EY").Resize(Cnt)
myMax = Application.Max(rngD)
Set rngO = .Cells(First, "FK").Resize(Cnt)
If myMax 0 Then
rngO.Value = myMax
End If
End With
End Sub

Claus B.
Office 2016