I am providing you with the code of my macro and hope that somebody can tell me what is making my macro slow and provide me with a solution as to how to make it run faster. Currently the execution of this code is taking ~ 1 min to finish but I still need to improve the execution time, any help will be highly appreciated. Below is the code:
Dim con As ADODB.Connection Dim rs As ADODB.Recordset Dim query As String Dim Fond As String Dim KontoNr As String Dim StartDate As Date Dim EndDate As Date Dim wb As Workbook Dim wr As Worksheet Dim ws As Worksheet Dim wt As Worksheet Application.Calculation = xlCalculationManual Application.ScreenUpdating = False Application.DisplayStatusBar = False Application.EnableEvents = False ActiveSheet.DisplayPageBreaks = False Set wb = ActiveWorkbook Set wr = Sheets("Fee") Set ws = Sheets("TestExecution") Set wt = Sheets("Results_Overview") 'wr.UsedRange.Interior.ColorIndex = 0 With wr.UsedRange RowCount = .Rows.Count If (RowCount > 1) Then wr.Range(2 & ":" & RowCount).EntireRow.Delete End If End With With wt.UsedRange RowCount = .Rows.Count If (RowCount > 2) Then wt.Range(2 & ":" & RowCount).EntireRow.Delete End If End With With ws.UsedRange ws.Range(Cells(2, 1), Cells(.Rows.Count, 1)).ClearContents ws.Range(Cells(2, 6), Cells(.Rows.Count, 15)).ClearContents End With Dim r As Long Dim Count As Integer Dim a As Integer Dim Counter As Integer Set con = New ADODB.Connection Set rs = New ADODB.Recordset PeriodStartDate = ws.Cells(2, 4).Value PeriodEndDate = ws.Cells(3, 4).Value KontoNr = ws.Cells(4, 4).Value Count = DatePart("d", PeriodEndDate) strCon = "Provider=SQLOLEDB; " & _ "Data Source= XXX;" & _ "Initial Catalog=XX;" & _ "Integrated Security=SSPI" con.Open (strCon) query = "SELECT distinct Fond FROM RI_Trans_Akt ta WITH (NOLOCK) WHERE cast(ta.Avslutsdag as date) < '" & PeriodEndDate & "'" rs.Open query, con, adOpenStatic con.Execute query Counter = rs.RecordCount ws.Cells(2, 1).CopyFromRecordset rs rs.Close con.Close Dim p As Long Dim lp As Long For p = 2 To Counter + 1 StartDate = ws.Cells(2, 4).Value a = wr.Range("A" & wr.Rows.Count).End(xlUp).Row For r = 1 To Count Fond = ws.Cells(p, 1).Value wr.Cells(a + r, 1).Value = Fond wr.Cells(a + r, 2).Value = StartDate wt.Cells(a + r, 1).Value = Fond wt.Cells(a + r, 2).Value = StartDate DateFormat = Format(StartDate, "yyyymmdd") con.Open (strCon) query = "select Totalt_Antal_Andelar,Forvaltnings_avgift,CAST(Forvaltnings_avgift_kurs AS NUMERIC(30,10)) AS Forvaltnings_avgift_Kurs from ri_fond_avgift WITH (NOLOCK) where Datum = '" & StartDate & "' and Fond = '" & Fond & "'" rs.Open query, con con.Execute query If (rs.RecordCount > 0) Then wr.Cells(a + r, 3).Value = rs.Fields(0) wr.Cells(a + r, 4).Value = rs.Fields(1) wr.Cells(a + r, 5).Value = rs.Fields(2) Else wr.Cells(a + r, 3).Value = "0.00" wr.Cells(a + r, 4).Value = "0.00" wr.Cells(a + r, 5).Value = "0.00" End If rs.Close query = "SELECT ta.KontoNr,Sum (Antal_andelar) FROM RI_Trans_Akt ta WITH (NOLOCK) WHERE ta.Kontonr = '" & KontoNr & "' and cast(ta.Avslutsdag as date) < '" & StartDate & "' and ta.Fond = '" & Fond & "' and ta.Mak_dag is null Group BY ta.Kontonr,ta.Fond" rs.Open query, con, adOpenStatic con.Execute query If (rs.RecordCount > 0) Then wr.Cells(a + r, 6).Value = rs.Fields(0) wr.Cells(a + r, 7).Value = rs.Fields(1) Else wr.Cells(a + r, 7).Value = "0.00" End If rs.Close con.Close StartDate = DateAdd("d", 1, StartDate) Next r Dim i As Integer For i = a + 1 To Count + a If (wr.Cells(i, 3).Value <> 0) Then wr.Cells(i, 8).Value = wr.Cells(i, 5).Value * wr.Cells(i, 7).Value wt.Cells(i, 3).Value = wr.Cells(i, 8).Value Else wr.Cells(i, 5).Value = "0.00" wr.Cells(i, 8).Value = "0.00" wt.Cells(i, 3).Value = "0.00" End If Next i Dim j As Integer Dim totalManagementFee As Double totalManagementFee = 0 For j = a + 1 To Count + a totalManagementFee = totalManagementFee + wr.Cells(j, 8).Value Next j ws.Cells(p, 7).Value = totalManagementFee ws.Cells(p, 6).Value = Fond Next p Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True Application.DisplayStatusBar = True Application.EnableEvents = True ActiveSheet.DisplayPageBreaks = True End Sub
✓ Extra quality
ExtraProxies brings the best proxy quality for you with our private and reliable proxies
✓ Extra anonymity
Top level of anonymity and 100% safe proxies – this is what you get with every proxy package
✓ Extra speed
1,ooo mb/s proxy servers speed – we are way better than others – just enjoy our proxies!
USA proxy location
We offer premium quality USA private proxies – the most essential proxies you can ever want from USA
Our proxies have TOP level of anonymity + Elite quality, so you are always safe and secure with your proxies
Use your proxies as much as you want – we have no limits for data transfer and bandwidth, unlimited usage!
Superb fast proxy servers with 1,000 mb/s speed – sit back and enjoy your lightning fast private proxies!
99,9% servers uptime
Alive and working proxies all the time – we are taking care of our servers so you can use them without any problems
No usage restrictions
You have freedom to use your proxies with every software, browser or website you want without restrictions
Perfect for SEO
We are 100% friendly with all SEO tasks as well as internet marketing – feel the power with our proxies
Buy more proxies and get better price – we offer various proxy packages with great deals and discounts
We are working 24/7 to bring the best proxy experience for you – we are glad to help and assist you!