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
Sub AdjustGridlineThickness() 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 End If If cell.Borders(xlEdgeTop).LineStyle <> xlNone Then cell.Borders(xlEdgeTop).Weight = xlThick ' Adjust the thickness for top gridline End If If cell.Borders(xlEdgeRight).LineStyle <> xlNone Then cell.Borders(xlEdgeRight).Weight = xlThick ' Adjust the thickness for right gridline End If If cell.Borders(xlEdgeBottom).LineStyle <> xlNone Then cell.Borders(xlEdgeBottom).Weight = xlThick ' Adjust the thickness for bottom gridline End If Next cell End If Application.ScreenUpdating = True End Sub
- 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)||xlHairline|
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.