title: VBA to draw borders
body: <p>Below is the code that i use on one of my spreadsheet to do the formatting and it works without any error.</p>
  
  <p>It's bit lengthy because I got this from Macro Recorder and modified a bit.</p>
  
  <p>The problem I am encountering with this script is that it takes around 5 to 10 seconds to get the job done.</p>
  
  <p>Is there anyway to shorten this code and speed up the process?</p>
  
  <pre><code>Sub FORMAT()
  
   Application.ScreenUpdating = False
  
  
  Range("B5:EM5000").Select
  Selection.Borders(xlEdgeLeft).LineStyle = xlNone
      Selection.Borders(xlEdgeTop).LineStyle = xlNone
      Selection.Borders(xlEdgeBottom).LineStyle = xlNone
      Selection.Borders(xlEdgeRight).LineStyle = xlNone
      Selection.Borders(xlInsideVertical).LineStyle = xlNone
      Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
  
      '''''
      Range("B5:D5").Select
      Range(Selection, Selection.End(xlDown)).Select
  
      With Selection.Borders(xlEdgeLeft)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeTop)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeBottom)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeRight)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlInsideVertical)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
      With Selection.Borders(xlInsideHorizontal)
          .LineStyle = xlContinuous
         .TintAndShade = 0
          .Weight = xlHairline
      End With
  
        '''''
      Range("B5:c5").Select
      Range(Selection, Selection.End(xlDown)).Offset(0, 3).Select
  
     With Selection.Borders(xlEdgeLeft)
          .LineStyle = xlDouble
       .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeTop)
          .LineStyle = xlDouble
         .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeBottom)
          .LineStyle = xlDouble
         .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeRight)
          .LineStyle = xlDouble
         .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlInsideVertical)
          .LineStyle = xlContinuous
         .TintAndShade = 0
          .Weight = xlHairline
      End With
      With Selection.Borders(xlInsideHorizontal)
          .LineStyle = xlContinuous
         .TintAndShade = 0
          .Weight = xlHairline
      End With
     '''''
   Range("B5:c5").Select
      Range(Selection, Selection.End(xlDown)).Resize(, 25).Offset(0, 5).Select
    With Selection.Borders(xlEdgeLeft)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeTop)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeBottom)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeRight)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlInsideVertical)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
      With Selection.Borders(xlInsideHorizontal)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
  
  
        '''''
  
      Range("B5:c5").Select
      Range(Selection, Selection.End(xlDown)).Resize(, 11).Offset(0, 27).Select
  
  
      With Selection.Borders(xlEdgeLeft)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeTop)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeBottom)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeRight)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlInsideVertical)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
      With Selection.Borders(xlInsideHorizontal)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
  
  
        '''''
  
        '''''
  
      Range("B5:l5").Select
      Range(Selection, Selection.End(xlDown)).Offset(0, 39).Select
  
  
      With Selection.Borders(xlEdgeLeft)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeTop)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeBottom)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeRight)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlInsideVertical)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
      With Selection.Borders(xlInsideHorizontal)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
  
  
  
  
  
       ''''''''''''''
  
        '''''
  
      Range("B5:k5").Select
      Range(Selection, Selection.End(xlDown)).Offset(0, 50).Select
  
  
      With Selection.Borders(xlEdgeLeft)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeTop)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeBottom)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeRight)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlInsideVertical)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
      With Selection.Borders(xlInsideHorizontal)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
  
  
  
  
  
       ''''''''''''''
  
        '''''
  
      Range("B5:k5").Select
      Range(Selection, Selection.End(xlDown)).Offset(0, 60).Select
  
  
      With Selection.Borders(xlEdgeLeft)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeTop)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeBottom)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeRight)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlInsideVertical)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
      With Selection.Borders(xlInsideHorizontal)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
  
  
  
  
  
       ''''''''''''''
        '''''
  
      Range("B5:k5").Select
      Range(Selection, Selection.End(xlDown)).Offset(0, 70).Select
  
  
      With Selection.Borders(xlEdgeLeft)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeTop)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeBottom)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeRight)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlInsideVertical)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
      With Selection.Borders(xlInsideHorizontal)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
  
  
  
  
  
       ''''''''''''''
  
         '''''
  
      Range("B5:k5").Select
      Range(Selection, Selection.End(xlDown)).Offset(0, 80).Select
  
  
      With Selection.Borders(xlEdgeLeft)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeTop)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeBottom)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeRight)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlInsideVertical)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
      With Selection.Borders(xlInsideHorizontal)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
  
  
  
  
  
       ''''''''''''''
  
  
  
  
         '''''
  
      Range("B5:k5").Select
      Range(Selection, Selection.End(xlDown)).Offset(0, 90).Select
  
  
      With Selection.Borders(xlEdgeLeft)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeTop)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeBottom)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeRight)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlInsideVertical)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
      With Selection.Borders(xlInsideHorizontal)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
  
  
  
  
  
       ''''''''''''''
  
  
  
  
  
         '''''
  
      Range("B5:k5").Select
      Range(Selection, Selection.End(xlDown)).Offset(0, 100).Select
  
  
      With Selection.Borders(xlEdgeLeft)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeTop)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeBottom)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeRight)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlInsideVertical)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
      With Selection.Borders(xlInsideHorizontal)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
  
  
  
  
  
       ''''''''''''''
  
  
      Range("B5:k5").Select
      Range(Selection, Selection.End(xlDown)).Offset(0, 110).Select
  
  
      With Selection.Borders(xlEdgeLeft)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeTop)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeBottom)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeRight)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlInsideVertical)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
      With Selection.Borders(xlInsideHorizontal)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
  
  
  
  
  
       ''''''''''''''
  
       ''''''''''''''
  
  
      Range("B5:k5").Select
      Range(Selection, Selection.End(xlDown)).Offset(0, 120).Select
  
  
      With Selection.Borders(xlEdgeLeft)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeTop)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeBottom)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeRight)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlInsideVertical)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
      With Selection.Borders(xlInsideHorizontal)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
  
  
  
  
  
       ''''''''''''''
  
       ''''''''''''''
  
  
      Range("B5:k5").Select
      Range(Selection, Selection.End(xlDown)).Offset(0, 130).Select
  
  
      With Selection.Borders(xlEdgeLeft)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeTop)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeBottom)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeRight)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlInsideVertical)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
      With Selection.Borders(xlInsideHorizontal)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
  
     ''''''''
      ''''''''''''''
  
  
      Range("B5:k5").Select
      Range(Selection, Selection.End(xlDown)).Offset(0, 140).Select
  
  
      With Selection.Borders(xlEdgeLeft)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeTop)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeBottom)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeRight)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlInsideVertical)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
      With Selection.Borders(xlInsideHorizontal)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
  
      Range("B5").Select
      Range(Selection, Selection.End(xlDown)).Offset(0, 38).Select
  
  
      With Selection.Borders(xlEdgeLeft)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeTop)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeBottom)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlEdgeRight)
          .LineStyle = xlDouble
  
          .TintAndShade = 0
          .Weight = xlThick
      End With
      With Selection.Borders(xlInsideVertical)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
      With Selection.Borders(xlInsideHorizontal)
          .LineStyle = xlContinuous
  
          .TintAndShade = 0
          .Weight = xlHairline
      End With
  
  
  
      ''''''''
  
  
  
  
       ''''''''''''''
        Range("AP5").Select
      Range(Selection, Selection.End(xlDown)).Select
  
      Selection.Rows.AutoFit
  
  
       ''''''''''''''
  
        Range("e:f").Select
      Range(Selection, Selection.End(xlDown)).Select
       Selection.NumberFormat = "mmm-yy;@"
  
       Range("g:h").Select
        Range(Selection, Selection.End(xlDown)).Select
         Selection.NumberFormat = "#,##0"
  
  
  
  
  
      ''''''
  
     Range("B5:EM5000").Select
  
      With Selection.Font
          .Name = "Calibri"
          .SIZE = 8
  
      End With
  
  
  
  
       Application.ScreenUpdating = True
  
  End Sub
  </code></pre>