Adjusting the thickness of all borders within a worksheet in Excel can be a time-consuming and tedious task. In this article, we will discuss a macro that allows you to change the thickness of all borders within a worksheet in Excel in bulk.
The macro we have created this time is designed to change the thickness of borders set on a single sheet to the ‘thickest line’ in bulk. While it is easy to modify the border thickness for table-style borders using regular functions, it becomes a manual process to modify borders that are set inconsistently across a sheet, such as in a family tree or organizational chart. This can be very time-consuming.
By automating this process, you can save valuable time and effort.
Macro to change the thickness of all borders set on a sheet
Dim ws As Worksheet
Dim rng As Range
Dim lastCell As Range
Dim cell As Range
Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with your actual sheet name
Application.ScreenUpdating = False
' Find the last cell with data in the worksheet
Set lastCell = ws.Cells.SpecialCells(xlLastCell).Cells
If Not lastCell Is Nothing Then
' Define the range to validate based on the last cell with data
Set rng = ws.Range("A1", lastCell)
' Iterate through each cell in the defined range
For Each cell In rng
' Check if the cell has existing gridlines
If cell.Borders(xlEdgeLeft).LineStyle <> xlNone Then
cell.Borders(xlEdgeLeft).Weight = xlThick ' Adjust the thickness for left gridline
If cell.Borders(xlEdgeTop).LineStyle <> xlNone Then
cell.Borders(xlEdgeTop).Weight = xlThick ' Adjust the thickness for top gridline
If cell.Borders(xlEdgeRight).LineStyle <> xlNone Then
cell.Borders(xlEdgeRight).Weight = xlThick ' Adjust the thickness for right gridline
If cell.Borders(xlEdgeBottom).LineStyle <> xlNone Then
cell.Borders(xlEdgeBottom).Weight = xlThick ' Adjust the thickness for bottom gridline
Application.ScreenUpdating = True
- Please remember to back up your data before executing this macro.
- Replace ‘sheet1’ with the actual name of the sheet you intend to use.
This macro will change all borders within the sheet to the specified thickness in bulk. For example, it will change the borders from their original thickness (thin lines) to the thickest line.
Types of border thickness
There are four types of border thickness. They can be represented as ‘Weight = xlThick’ and you can freely set the thickness by replacing ‘xlThick’ with any of the following options:
|The thinnest border (invisible)
Please make use of this macro when dealing with situations where there are random variations in the thickness of borders, such as in organizational charts.
Creating a macro allows you to save more time compared to manual work.