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
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
You can use:
ActiveSheet.UsedRange.BorderAround xlContinuous, xlMedium
I believe this will do it.
