VBA to draw borders

Question

Below is the code that i use on one of my spreadsheet to do the formatting and it works without any error.

It's bit lengthy because I got this from Macro Recorder and modified a bit.

The problem I am encountering with this script is that it takes around 5 to 10 seconds to get the job done.

Is there anyway to shorten this code and speed up the process?

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

The question is a bit broad to answer. But I suggest not to format the sheet until the last row. I'm sure you will never use the full `1 048 576` rows. Limit formatting up to row 1000 or something would be much faster. And you don't need to select the cells. You can format them directly e.g. `With Range("B5:D5").Borders(xlEdgeLeft)`. — Pᴇʜ, Mar 03 '17 at 10:57
Don't select the cells first, that is what is taking extra time. Just work with the Range-objects directly, e.g. `Range("B5:C5").Borders.Selection.Borders(xlEdgeLeft).LineStyle = xlDouble`. For more information, please see [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/a/10717999/1490783) — Olle Sjögren, Mar 03 '17 at 11:01
@Peh I have another VBA code which inserts data to columns B:D, and the number of rows varies, this is the reason why i used "Last row" here. So i think writing just `Range("B5:D5")` without "Last row is not the right way to cover all the data in columns B:D — Sahal, Mar 03 '17 at 12:35
@Sahal `Range("B5:D5")` was just an example how to avoid using `Select` which consumes time. Using last row is definitely time consumptive too. If the number of rows varies try to calculate this number first, then you know how many rows you need to format. Or if you say it's always less than 1000 then format 1000 but avoid formatting 1 million! Try to format only rows you really need to format. You don't paint the whole house if you only need to paint one room. — Pᴇʜ, Mar 03 '17 at 13:23
@Sahal Instead to make sélections, try to use range object, like, to make borders in a all the range : with thisworkbook.Worksheets(theSheet) Set rng = .Range(.Cells(firstRow, firstColumn), .Cells(lastRow, lastColumn)) end with rng.Borders.LineStyle = xlContinuous — D. O., Mar 03 '17 at 21:04
@Sahal your code is not really a code. What you want to do is fairly simple, but I am having a hard time to see what you want to do. I suggest that you learn how to format a range without really trying to solve this problem first. Learn about how to define ranges in VBA and then you can take care of the rest. To find the last row dynamically, it is only one line of code that you can find everywhere on the internet. If you do it right, it will take 1 second to do the job. Your code on my computer did not finish in one minute, so your computer must be fast — Ibo, Mar 03 '17 at 22:23
@Ibo What you said is right, since i am new to Excel VBA I have very little knowledge of writing script I dont think the code above will take more than a minute to do the formatting..Probably you didn't have any data in column B:D starting from B5:D5..? — Sahal, Mar 03 '17 at 22:59
In simple words, basically first i want the script to remove all the existing border lines in Sheet1 and then apply border lines (like the ones in the above code i posed) to data starting from B5: to the last non blank cell in column B. How do i do this? — Sahal, Mar 03 '17 at 23:18
@Sahal since you are new to the forum, do NOT forget to hit the checkmark next to the answers, if they are correct — Ibo, Mar 04 '17 at 00:20

score 1 · Answer 1 · answered Dec 13 '18 at 17:37

I use this code to erase the borders in a selected area and redraw a thin border around the same area. Not sure if this will help anyone.

Sub noborder_border()
'
' noborder_border Macro
'
' Keyboard Shortcut: Ctrl+Shift+B
'
    With Selection
    .Borders(xlEdgeLeft).LineStyle = xlNone
    .Borders(xlEdgeRight).LineStyle = xlNone
    .Borders(xlEdgeTop).LineStyle = xlNone
    .Borders(xlEdgeBottom).LineStyle = xlNone
    .Borders(xlInsideVertical).LineStyle = xlNone
    .Borders(xlInsideHorizontal).LineStyle = xlNone
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
   End With

   With Selection
    .Borders(xlEdgeLeft).LineStyle = xlContinuous
    .Borders(xlEdgeLeft).Weight = xlThin
    .Borders(xlEdgeRight).LineStyle = xlContinuous
    .Borders(xlEdgeRight).Weight = xlThin
    .Borders(xlEdgeTop).LineStyle = xlContinuous
    .Borders(xlEdgeTop).Weight = xlThin
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
    .Borders(xlEdgeBottom).Weight = xlThin
   End With
 End Sub

score 0 · Accepted Answer · answered Mar 04 '17 at 00:20

Think about borders as lines: Top, Right, Bottom, Left, Vertical lines (inside the range) and Horizontal lines (inside the range)

One line of code will draw all of the lines for the range. You can modify them to get what you want.

To have a clean code and better control on the execution of code, you should learn to write functions, as an example, I wrote a function that gives you the last row in a given column in a given worksheet.

Sub DrawBorder()
    Dim lRow As Integer
    Dim cell As Range
    Dim rng As Range
    Dim WS As Worksheet

    Set WS = ActiveSheet 'you can set this to a specific sheet like Set WS=Sheets("Sheet1")

    'Clear all of the borders in the sheet
    WS.Cells.Borders.LineStyle = xlNone

    'Find the last row in column B=2
    lRow = LastRowInColumn(WS, 2)

    Set rng = WS.Range("B5:D" & lRow)

    'Borders of the cells inside the range
    rng.Borders.LineStyle = xlDot

    'Border of the range as a whole with double lines
    rng.Borders(xlEdgeTop).LineStyle = xlDouble
    rng.Borders(xlEdgeBottom).LineStyle = xlDouble
    rng.Borders(xlEdgeLeft).LineStyle = xlDouble
    rng.Borders(xlEdgeRight).LineStyle = xlDouble

'    'You can use these lines to remove the vertical/horizontal lines isnide a range
'    rng.Borders(xlInsideVertical).LineStyle = xlNone
'    rng.Borders(xlInsideHorizontal).LineStyle = xlNone

End Sub


Function LastRowInColumn(Optional sh As Worksheet, Optional colNumber As Long = 1) As Long
    'Finds the last row in a particular column which has a value in it
    If sh Is Nothing Then
        Set sh = ActiveSheet
    End If
    LastRowInColumn = sh.Cells(sh.Rows.Count, colNumber).End(xlUp).Row
End Function

score 0 · Answer 3 · edited May 02 '18 at 20:41

0

Sub Gray_Grid_Lines()

' This sets all the grid lines to a light gray

Cells.Select

    With Selection
          .Borders.LineStyle = xlContinuous
          .Borders.ThemeColor = 1
          .Borders.TintAndShade = -0.15
          .Borders.Weight = xlThin
    End With

Range("A1").Select

End Sub

edited May 02 '18 at 20:41

Paul Roub

36,322
27
84
93

answered May 02 '18 at 20:25

Zenwood

1

score 0 · Answer 4 · answered Oct 22 '21 at 21:04

Border drawing can be performed quickly using a loop.The start and end column number of the loop is specified:

For m = 0 To LbCols
With Sheets("YourSheet").Cells(Rows.Count, 1).End(xlUp).Offset(0, m).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = 23
End With
Next

Source

VBA to draw borders

4 Answers4