I have a file in the size of 13 MB. When I run my macro it calculates the first rows in 3 different worksheets and than I use AutoFill technique to next to 6000 rows and it takes to long time, something between 7-8 minutes. Most of the formulas are simple and should not take to long time, in my opinion.
Someone has solution to my problem? Maybe a way that the calculation itself will happen in the module and in the workbook we will see just the result, the values?
My code starts with a userform and then calls to a module, that takes most of the time that the macro runs. as I said each of the 3 sheets includes next to 6000 rows. I want that in the end in the worksheet I I will see just the values.
Sub Formulas()
Call Columns
LR = VacationWS.Cells(Rows.Count, "A").End(xlUp).Row
With VacationWS
'Column D
MyCol = .Range("D2").Column
ColumnSpace = MonthCol - MyCol
.Range("D2").FormulaR1C1 = _
"=SUMIFS(Visual!C[" & ColumnSpace & "],Visual!C[-3],VacationWS!RC[-3],Visual!C[5],Lists!R3C3,Visual!C[4],Lists!R2C5)"
'Column E
MyCol = .Range("E2").Column
ColumnSpace = MonthBefore - MyCol
.Range("E2").FormulaR1C1 = _
"=SUMIFS(Visual!C[" & ColumnSpace & "],Visual!C[-4],VacationWS!RC[-4],Visual!C[4],Lists!R4C3,Visual!C[3],Lists!R5C5)"
'Column F
MyCol = .Range("F2").Column
ColumnSpace = MonthCol - MyCol
.Range("F2").FormulaR1C1 = _
"=SUMIFS(Visual!C[" & ColumnSpace & "],Visual!C[-5],VacationWS!RC[-5],Visual!C[3],Lists!R2C3,Visual!C[2],Lists!R2C5)"
'Column G
.Range("G2").FormulaR1C1 = "=RC[-3]+RC[-2]-RC[-1]"
'Column H
MyCol = .Range("H2").Column
ColumnSpace = MonthCol - MyCol
.Range("H2").FormulaR1C1 = _
"=SUMIFS(Visual!C[" & ColumnSpace & "],Visual!C[-7],VacationWS!RC[-7],Visual!C[1],Lists!R4C3,Visual!C,Lists!R5C5)"
'Column I
.Range("I2").FormulaR1C1 = "=RC[-1]-RC[-2]"
'Delete unnecessary rows
.Range("D2:I2").AutoFill Destination:=VacationWS.Range("D2:I" & LR), Type:=xlFillDefault
.Range("A1:I1").AutoFilter
.Range("$A$1:$I$" & LR).AutoFilter Field:=9, Criteria1:="0"
On Error Resume Next
.Range("A2:I" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.ShowAllData
End With
With IllnessWS
'Column D
MyCol = .Range("D2").Column
ColumnSpace = MonthCol - MyCol
.Range("D2").FormulaR1C1 = _
"=SUMIFS(Visual!C[" & ColumnSpace & "],Visual!C[-3],IllnessWS!RC[-3],Visual!C[5],Lists!R3C3,Visual!C[4],Lists!R3C5)"
'Column E
MyCol = .Range("E2").Column
ColumnSpace = MonthBefore - MyCol
.Range("E2").FormulaR1C1 = _
"=SUMIFS(Visual!C[" & ColumnSpace & "],Visual!C[-4],IllnessWS!RC[-4],Visual!C[4],Lists!R4C3,Visual!C[3],Lists!R6C5)"
'Column F
MyCol = .Range("F2").Column
ColumnSpace = MonthCol - MyCol
.Range("F2").FormulaR1C1 = _
"=SUMIFS(Visual!C[" & ColumnSpace & "],Visual!C[-5],IllnessWS!RC[-5],Visual!C[3],Lists!R2C3,Visual!C[2],Lists!R3C5)"
'Column G
.Range("G2").FormulaR1C1 = "=RC[-3]+RC[-2]-RC[-1]"
'Column H
MyCol = .Range("H2").Column
ColumnSpace = MonthCol - MyCol
.Range("H2").FormulaR1C1 = _
"=SUMIFS(Visual!C[" & ColumnSpace & "],Visual!C[-7],IllnessWS!RC[-7],Visual!C[1],Lists!R4C3,Visual!C,Lists!R6C5)"
'Column I
.Range("I2").FormulaR1C1 = "=RC[-1]-RC[-2]"
'Delete unnecessary rows
.Range("D2:I2").AutoFill Destination:=IllnessWS.Range("D2", "I" & LR), Type:=xlFillDefault
.Range("A1:I1").AutoFilter
.Range("$A$1:$I$" & LR).AutoFilter Field:=9, Criteria1:="0"
On Error Resume Next
.Range("A2:I" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.ShowAllData
'Delete what is equal to 90 or more in column H
.Range("$A$1:$I$" & LR).AutoFilter Field:=8, Criteria1:=">=90", Operator:=xlAnd
.Range("A2:I" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.ShowAllData
End With
With HealingWS
'Column D
MyCol = .Range("D2").Column
ColumnSpace = MonthCol - MyCol
.Range("D2").FormulaR1C1 = _
"=SUMIFS(Visual!C[" & ColumnSpace & "],Visual!C[-3],HealingWS!RC[-3],Visual!C[5],Lists!R3C3,Visual!C[4],Lists!R4C5)"
'Column E
MyCol = .Range("E2").Column
ColumnSpace = MonthBefore - MyCol
.Range("E2").FormulaR1C1 = _
"=SUMIFS(Visual!C[" & ColumnSpace & "],Visual!C[-4],HealingWS!RC[-4],Visual!C[4],Lists!R4C3,Visual!C[3],Lists!R7C5)"
'Column F
.Range("F2").FormulaR1C1 = "=RC[-1]+RC[-2]"
'Column G
MyCol = .Range("G2").Column
ColumnSpace = MonthCol - MyCol
.Range("G2").FormulaR1C1 = _
"=SUMIFS(Visual!C[" & ColumnSpace & "],Visual!C[-6],HealingWS!RC[-6],Visual!C[2],Lists!R4C3,Visual!C[1],Lists!R7C5)"
'Column H
.Range("H2").FormulaR1C1 = "=RC[-1]-RC[-2]"
'Delete unnecessary rows
.Range("D2:H2").AutoFill Destination:=HealingWS.Range("D2:H" & LR), Type:=xlFillDefault
.Range("A1:H1").AutoFilter
.Range("$A$1:$H$" & LR).AutoFilter Field:=8, Criteria1:="0"
On Error Resume Next
.Range("A2:H" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.ShowAllData
'Specific for June in HealingWS (Goes to Null)
With Lists
If MonthName = .Range("A7").Value Then
HealingWS.Range("E2").Value = 0
HealingWS.Range("E2").AutoFill Destination:=HealingWS.Range("E2:E" & LR), Type:=xlFillDefault
End If
End With
End With
'January formulas change
If MonthName = Lists.Range("A2").Value Then
With VacationWS
.Range("E2").FormulaR1C1 = _
"=SUMIFS(December!C[7],December!C[-4],VacationWS!RC[-4],December!C[4],Lists!R4C3,December!C[3],Lists!R5C5)"
.Range("E2").AutoFill Destination:=VacationWS.Range("E2:E" & LR), Type:=xlFillDefault
End With
With IllnessWS
.Range("E2").FormulaR1C1 = _
"=SUMIFS(December!C[7],December!C[-4],îçìä!RC[-4],December!C[4],Lists!R4C3,December!C[3],Lists!R6C5)"
.Range("E2").AutoFill Destination:=IllnessWS.Range("E2:E" & LR), Type:=xlFillDefault
End With
With HealingWS
.Range("E2").FormulaR1C1 = _
"=SUMIFS(December!C[7],December!C[-4],HealingWS!RC[-4],December!C[4],Lists!R4C3,December!C[3],Lists!R7C5)"
.Range("E2").AutoFill Destination:=HealingWS.Range("E2:E" & LR), Type:=xlFillDefault
End With
End If
End Sub
