title: Gantt chart on excel VBA code review and optimization
body: <p>I made an excel gantt chart with the help of VBA. It is my first VBA project and I did my best to make it clean and organized however I think a lot of optimizations could be done.</p>
  
  <p>I would like to ask you guys if you could take a look at some parts of my code that I have identified as the slowest and tell me if there is a better way : (Screenupdate, calculationautomatic, events, animation are all off and all my variables are declared as public and calculated in an other module)</p>
  
  <p>The overall macro runs in 1s. I know it isn't that long but it is called everytime a change is manually made on the sheet. So I'd like to bring it down as much as possible.</p>
  
  <p>Macro 1 takes about 0.2s in a 1s overall macro</p>
  
  <pre><code>Sub emptycells ()
  ' 
  ' This macro empty the cells after finding some text in a row
  
  For i = 1 To X
  
  If IsEmpty(Cells(line + i, col1)) = False Then
  
  coltext = WorksheetFunction.Match(Cells(line + i, col1), Range(Cells(line + i, col2), Cells(line + i, col3)), 0)
  Dim rngcelltext as Range
  Set rngcelltext = Cells(line + i, coltext)
  Range(rngcelltext, rngcelltext.End(xlToRight)).ClearContents
  
  End If
  
  Next i
  
  End Sub
  </code></pre>
  
  <p>Macro 2 takes about 0.25s in a 1s overall macro
  <a href="https://i.stack.imgur.com/Br9CS.png" rel="nofollow noreferrer">Top line is the one added by this macro</a></p>
  
  <pre><code>Sub addweeknb()
  '
  ' Weekline is the line N° of the second line in the picture, colweek1 is the first column of that line, rngweek is the whole line
  ' This macro add a line with week number on top of the time line
  
  For i = firstweek To lastweek
  
  numcol1 = WorksheetFunction.Match(i, rngweek, 1) 'find the column nb 
  nbcol = WorksheetFunction.CountIf(rngweek, i)
  Set rngweek_i = Range(Cells(weekline, colweek1 + numcol1 - nbcol), Cells(weekline, colweek1 + numcol1 - 1))
      With rngweek_i
          .HorizontalAlignment = xlCenter
          .VerticalAlignment = xlCenter
          .WrapText = False
          .Orientation = 0
          .AddIndent = False
          .IndentLevel = 0
          .ShrinkToFit = False
          .ReadingOrder = xlContext
          .MergeCells = False
      End With
          With rngsemi.Font
          .Name = "Calibri"
          .Size = 12
          .Strikethrough = False
          .Superscript = False
          .Subscript = False
          .OutlineFont = False
          .Shadow = False
          .Underline = xlUnderlineStyleNone
          .ThemeColor = xlThemeColorLight1
          .TintAndShade = 0
          .ThemeFont = xlThemeFontMinor
      End With
      rngweek_i.Merge
      rngweek_i.Font.Bold = True
      rngweek_i = i
  
  Next i
  
  End Sub
  </code></pre>
  
  <p>Edit : I managed to bring Macro 2 down to between 0.05s and 0.08s just by cleaning it a bit :</p>
  
  <p>Revised Macro 2 :</p>
  
  <pre><code>Sub addweeknb()
  '
  ' Weekline is the line N° of the second line in the picture, colweek1 is the first column of that line, rngweek is the whole line
  ' This macro add a line with week number on top of the time line
  
  For i = firstweek To lastweek
  
  numcol1 = WorksheetFunction.Match(i, rngweek, 1) 'find the column nb 
  nbcol = WorksheetFunction.CountIf(rngweek, i)
  Set rngweek_i = Range(Cells(weekline, colweek1 + numcol1 - nbcol), Cells(weekline, colweek1 + numcol1 - 1))
     With rngweek_i
       .MergeCells = True
       .Font.Name = "Calibri"
       .Font.Size = 12
       .Font.Bold = True
       .Font.ThemeColor = xlThemeColorLight1
       .Value = i
  End With
  
  Next i
  
  End Sub
  </code></pre>
  
  <p>Macro 3 takes about 0.45s in a 1s overall macro</p>
  
  <pre><code>Sub dimgant()
  '
  ' This macro resize the gantt chart
  '
  
  ' copi the conditional formula in the gantt area as it is now
  Range(Cells(firstlinegantt, firstcolgantt), Cells(lastlinegantt, lastcolgantt)) = _
          "=MyFormula"
  
  
  ' Add lines to a liste with matricial formula in excel. It must match the number of line of the gantt
  rngfirstlinematformula.AutoFill Destination:=rngmatformule, Type:=xlFillDefault
  
  ' Add column if graph goes less than 10 days after the latest date of the project
  If lastcolgantt - firstcolgantt &lt; nbofcolumnneeded + 10 Then
  rnglastcol.AutoFill Destination:=Range(rnglastcol, Range(Cells(firstlinegantt, lastcolgantt), Cells(lastlinegantt, nbofcolumnneeded + 10))), Type:=xlFillDefault
  End If
  
  ' Delete column if graph goes further than 10 days after the latest date of the project
  If lastcolgantt - firstcolgantt &gt; nbofcolumnneeded + 10 Then
  Range(Cells(1, nbofcolumnneeded + 10), Cells(lastlinegantt, lastcolgantt)).Delete Shift:=xlToLeft
  End If
  
  
  End Sub
  </code></pre>
  
  <p>Thank you all very much.</p>