Hi
I have the following macro (as part of a much bigger set of code that adds some formulas to to cells to return the total number of unique values. The problem is, this part of the macro takes over 10 minutes to run. Is there a quicker way of doing this or am I going to have to just wait it out each time.
The columns B and/or H is a formula. Column W is an string value.
FYI the last row it is counting to is between 4000 and 30000 lines in each of the formulas
I have the following macro (as part of a much bigger set of code that adds some formulas to to cells to return the total number of unique values. The problem is, this part of the macro takes over 10 minutes to run. Is there a quicker way of doing this or am I going to have to just wait it out each time.
The columns B and/or H is a formula. Column W is an string value.
FYI the last row it is counting to is between 4000 and 30000 lines in each of the formulas
Code:
Dim IRMatRow As Integer
Dim IRMisRow As Integer
Dim UKMatRow As Integer
Dim UKMisRow As Integer
IRMatRow = Worksheets("Matching Stats").Cells(Rows.Count, "H").End(xlUp).Row
IRMisRow = Worksheets("MIS Report").Cells(Rows.Count, "B").End(xlUp).Row
UKMatRow = Worksheets("Matching Stats UK").Cells(Rows.Count, "H").End(xlUp).Row
UKMisRow = Worksheets("MIS Report UK").Cells(Rows.Count, "B").End(xlUp).Row
Range("C15").Value = "IR"
Range("C16").FormulaArray = "=SUM(IF(""System""='Matching Stats'!$W$3:$W$" & IRMatRow & ", 1/(COUNTIFS('Matching Stats'!$W$3:$W$" & IRMatRow & ", ""System"",'Matching Stats'!$H$3:$H$" & IRMatRow & ", 'Matching Stats'!$H$3:$H$" & IRMatRow & ")), 0))-1"
Range("C17").FormulaArray = "=SUM(IF(""System""<>'Matching Stats'!$W$3:$W$" & IRMatRow & ", 1/(COUNTIFS('Matching Stats'!$W$3:$W$" & IRMatRow & ", ""<>System"",'Matching Stats'!$H$3:$H$" & IRMatRow & ", 'Matching Stats'!$H$3:$H$" & IRMatRow & ")), 0))-1"
Range("C18").FormulaArray = "=SUM(1/COUNTIF('Matching Stats'!$H$3:$H$" & IRMatRow & ",'Matching Stats'!$H$3:$H" & IRMatRow & "))-1"
Range("C20").FormulaArray = "=SUM(1/COUNTIF('MIS Report'!$B$2:$HB" & IRMisRow & ",'MIS Report'!$B$2:$B$" & IRMisRow & "))-1"
Range("D15").Value = "UK"
Range("D16").FormulaArray = "=SUM(IF(""System""='Matching Stats UK'!$W$3:$W$" & UKMatRow & ", 1/(COUNTIFS('Matching Stats UK'!$W$3:$W$" & UKMatRow & ", ""System"",'Matching Stats UK'!$H$3:$H$" & UKMatRow & ", 'Matching Stats UK'!$H$3:$H$" & UKMatRow & ")), 0))-1"
Range("D17").FormulaArray = "=SUM(IF(""System""<>'Matching Stats UK'!$W$3:$W$" & UKMatRow & ", 1/(COUNTIFS('Matching Stats UK'!$W$3:$W$" & UKMatRow & ", ""<>System"",'Matching Stats UK'!$H$3:$H$" & UKMatRow & ", 'Matching Stats UK'!$H$3:$H$" & UKMatRow & ")), 0))-1"
Range("D18").FormulaArray = "=SUM(1/COUNTIF('Matching Stats UK'!$H$3:$H$" & UKMatRow & ",'Matching Stats UK'!$H$3:$H" & UKMatRow & "))-1"
Range("D20").FormulaArray = "=SUM(1/COUNTIF('MIS Report UK'!$B$3:$B$" & UKMisRow & ",'MIS Report UK'!$B$2:$B$" & UKMisRow & "))-1"