In general there are five rules to making Excel-VBA macros fast:
-
Don’t use
.Select
methods, -
Don’t use
Active*
objects more than once, -
Disable screen-updating and automatic calculations,
-
Don’t use visual Excel methods (like Search, Autofilter, etc),
-
And most of all, always use range-array copying instead of browsing individual cells in a range.
Of these, you have only implemented #3. Additionally, you are exacerbating things by re-Saving your worksheets, just so that you can execute Visual modification methods (AutoFilter in your case). What you need to do to make it fast is to first implement the rest of these rules, and secondly, stop modifying your source worksheets so that you can open them read-only.
The core of what’s causing your problems and forcing all of these other undesirable decisions is how you have implemented the Filters
function. Instead of trying to do everything with the visual Excel functions, which are slow compared to (well-written) VBA (and that modify the worksheets, forcing your redundant Saves), just range-array copy all of the data you need from the sheet and use straight-forward VBA code to do your counting.
Here is an example of your Filters
function that I converted to these principles:
Function Filters(ByRef values() As Variant, ByRef arryindex)
On Error GoTo 0
Dim ws As Worksheet
Set ws = ActiveSheet
'find the last cell that we might care about
Dim LastCell As Range
Set LastCell = ws.Range("B6:AZ6").End(xlDown)
'capture all of the data at once with a range-array copy
Dim data() As Variant, colors() As Variant
data = ws.Range("A6", LastCell).Value
colors = ws.Range("BC6", "BC" & LastCell.Row).Interior.Color
' now scan through every row, skipping those that do not
'match the filter criteria
Dim r As Long, c As Long, v As Variant
Dim TotCnt1 As Long, TotCnt2 As Long, TotCnt3 As Long, TotCnt4 As Long
TotCnt1 = -1: TotCnt2 = -1: TotCnt3 = -1: TotCnt4 = -1
For r = 1 To UBound(data, 1)
'filter column1 (B6[2])
v = data(r, 2)
If v = "p1" Or v = "p2" Or v = "p3" Or v = "p4" Or v = "p5" Then
'filter column2 (J6[10])
v = data(r, 10)
If v = "s1" Or v = "d2" Or d = "s3" Then
'get the total of points
TotCnt1 = TotCnt1 + 1
End If
'filter column2 for different criteria
If data(r, 10) = "s" Then
'filter colum3 for associated form
If CStr(data(r, 52)) <> "" Then
'get the total of points
TotCnt2 = TotCnt2 + 1
Else
' filter coum 3 for blank forms
'get the total of points
TotCnt3 = TotCnt3 + 1
End If
End If
'filter for column4 if deadline was made
v = data(r, 10)
If v = "s1" Or v = "s2" Or v = "s3" Or v = "s4" Or v = "s5" Then
If colors(r, 1) = RGB(146, 208, 80) Then
TotCnt4 = TotCnt4 + 1
End If
End If
End If
Next r
values(arryindex) = TotCnt1
values(arryindex + 1) = TotCnt2
values(arryindex + 2) = TotCnt3
values(arryindex + 3) = TotCnt4
arryindex = arryindex + 4
End Function
Please note that because I cannot test this for you and also because there is a lot of implicitness to the Autofilter/Range effects in the original code, I cannot tell if it is correct. You will have to do that.
Note: If you do decided to implement this, please let us know what impact it had, if any. (I try to keep track of what works and how much)