Trebor8484
Board Regular
- Joined
- Oct 27, 2018
- Messages
- 69
- Office Version
- 2013
- Platform
- Windows
Hi,
I have a workbook with approximately 500,000 rows which expands each month.
The below code will count each job reference number only once and any subsequent lines for the same reference will return as zero. This code is taking approximately 15 mins to finish the calculation on the column.
Can anyone suggest something that would run quicker please? I have looked into dictionary keys, and wondered if it may be possible to store each unique job reference in memory before outputting the results back to the sheet?
I have a workbook with approximately 500,000 rows which expands each month.
The below code will count each job reference number only once and any subsequent lines for the same reference will return as zero. This code is taking approximately 15 mins to finish the calculation on the column.
Can anyone suggest something that would run quicker please? I have looked into dictionary keys, and wondered if it may be possible to store each unique job reference in memory before outputting the results back to the sheet?
VBA Code:
Sub CountFirstInstance()
Dim sht As Worksheet
Set sht = ThisWorkbook.Sheets("Sheet1")
With sht.Range("Q2:Q" & sht.Cells(Rows.Count, "A").End(xlUp).Row)
.Formula = "=IF(COUNTIF($O$2:O2,O2)=1,1,0)"
End With
Do Until Application.CalculationState = xlDone
Loop
With sht.Range("Q2:Q" & sht.Cells(Rows.Count, "A").End(xlUp).Row)
.Value = .Value
End With
'Rest of code
End Sub