VBA Borders Excel Dependent on Page Size

Question

I want to create borders around each of my excel pages dependent on the size of the page e.g. number of rows and columns can vary all the time. I've tried this but it's specific cells

 Sub AddBorders()

 With Range("B8:I10")
     .Borders(xlEdgeLeft).LineStyle = xlContinuous
     .Borders(xlEdgeRight).LineStyle = xlContinuous
     .Borders(xlEdgeBottom).LineStyle = xlContinuous
     .Borders(xlEdgeTop).LineStyle = xlContinuous End With End Sub

Siddharth Rout Siddharth Rout · Accepted Answer · 2015-09-04T10:34:48

UsedRange should NEVER be used to find the last cell which has data. It is highly unreliable. . You may want to see THIS for the explanation on usedrange.

Always find the last row and the last column and then create the range. See this example.

I would recommend this way

Sub AddBorders()
    Dim ws As Worksheet
    Dim lastrow As Long, lastcol As Long

    Set ws = Sheet1 '<~~ Change as applicable

    With ws
        '~~> Check if sheet has any data
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            '~~> Get Last Row
            lastrow = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row

            '~~> Get Last Column
            lastcol = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByColumns, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Column

            '~~> Work with the range
            .Range(.Cells(1, 1), .Cells(lastrow, lastcol)).BorderAround _
            xlContinuous, xlMedium

        End If
    End With
End Sub

FOLLOWUP FROM COMMENTS

This works better. Only issue is border doesn't go around any graphs/charts. Is there a way to do that aswell? Thanks for your help – user1296762 7 mins ago

Also sorry can we have the bottom border last row+1 as some rows are grouped up and therefore line can't be seen if not expanded – user1296762 5 mins ago

Is this what you are trying?

Sub AddBorders()
    Dim ws As Worksheet
    Dim lastrow As Long, lastcol As Long
    Dim shp As Shape

    Set ws = Sheet1 '<~~ Change as applicable

    With ws
        '~~> Check if sheet has any data
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            '~~> Get Last Row
            lastrow = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row

            '~~> Get Last Column
            lastcol = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByColumns, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Column


        End If

        '~~> Loop through shapes and find the last row and column
        For Each shp In .Shapes
            If shp.BottomRightCell.Row > lastrow Then lastrow = shp.BottomRightCell.Row
            If shp.BottomRightCell.Column > lastcol Then lastcol = shp.BottomRightCell.Column
        Next

        If lastrow <> 0 And lastcol <> 0 Then
            'Also sorry can we have the bottom border last row+1 as some rows are
            'grouped up and therefore line can't be seen if not expanded
            '–  user1296762 2 mins ago
            lastrow = lastrow + 1: lastcol = lastcol + 1

            '~~> Work with the range
            .Range(.Cells(1, 1), .Cells(lastrow, lastcol)).BorderAround _
            xlContinuous, xlMedium
        End If
    End With
End Sub

Sceenshot

VBA Borders Excel Dependent on Page Size

2 Answers