title: How can I optimise the running speed of following VBA code?
body: <p>I want to improve the efficiency of following VBA code. What i can think about is to use "With" and "End With" in the code. Is there any thing else I can do to optimise the code?</p>
  
  <p>Thanks!</p>
  
  <pre><code> Sub ONLY_CORP_Lives_Premiums()
  Dim row_phil, row_phl, row_eveeb, row_evenb As Integer
  Dim row_indiv, row_sme, row_corp As Integer
  Dim row As Variant
  Dim row_start, row_end As Integer
  Dim i, k As Integer
  Dim start_time, end_time
  
  start_time = Now()
  Application.ScreenUpdating = False
  Application.Calculation = xlCalculationManual
  Sheets("SETUP").Range("J32") = Sheets("SETUP").Range("J28")
  
  Sheets("LIVES PROJECTION").Activate
  row_phil = Application.CountIf(Range("B1237:B1048576"), "PHIL" &amp; "*")
  row_phl = Application.CountIf(Range("B1237:B1048576"), "PHL" &amp; "*") + row_phil
  row_eveeb = Application.CountIf(Range("B1237:B1048576"), "EVEEB" &amp; "*") + row_phl
  row_evenb = Application.CountIf(Range("B1237:B1048576"), "EVENB" &amp; "*") + row_eveeb
  row = Array(0, row_phil, row_phl, row_eveeb, row_evenb)
  
  For i = LBound(row) To (UBound(row) - 1)
  
      row_start = row(i) + 34
      row_end = row(i + 1) - 1 + 34
      row_indiv = Application.CountIf(Range("d" &amp; row_start &amp; ":d" &amp; row_end), "INDIV")
      row_sme = Application.CountIf(Range("d" &amp; row_start &amp; ":d" &amp; row_end), "SME")
      row_corp = Application.CountIf(Range("d" &amp; row_start &amp; ":d" &amp; row_end), "CORP")
          For k = row_start + row_indiv + row_sme - 34 To row_start + row_indiv + row_sme + row_corp - 1 - 34
          With Sheets("LIVES PROJECTION")
          .Range("B12:G12").Value = .Range("B34:G34").Offset(k, 0).Value
          .Range("B12:FM26").Calculate
          End With
          Sheets("PREMIUMS PROJECTION").Range("B12:FM26").Calculate
          Sheets("LIVES PROJECTION").Range("H34:FM34").Offset(k, 0).Value = Sheets("LIVES PROJECTION").Range("H12:FM12").Value
          Sheets("PREMIUMS PROJECTION").Range("H34:FM34").Offset(k, 0).Value = Sheets("PREMIUMS PROJECTION").Range("H12:FM12").Value
          Next k
  Next i
  ''
  Application.ScreenUpdating = True
  Application.Calculation = xlCalculationAutomatic
  Sheets("SETUP").Range("J33") = Sheets("SETUP").Range("J28")
  Calculate!
  end_time = Now()
  Sheets("SETUP").Range("j50") = DateDiff("s", start_time, end_time)
  
  End Sub
  </code></pre>