VBA: Put Borders Around All Used Cells

Applying borders by hand is fine once. Doing it on twenty sheets, or every time a report is regenerated, is what macros are for. The whole job takes two lines. If VBA is new to you, our comparison of Excel courses is a reasonable place to start.

An outline around the used range

Sub OutlineUsedRange()
    ActiveSheet.UsedRange.BorderAround _
        LineStyle:=xlContinuous, Weight:=xlMedium
End Sub

BorderAround draws only the outer frame. It is a method of the Range object, so it works on any range — Range("B2:D10").BorderAround is equally valid.

The arguments you will actually use:

• LineStyle: xlContinuous, xlDash, xlDot, xlDouble or xlLineStyleNone
• Weight: xlHairline, xlThin, xlMedium or xlThick
• Color: an RGB value, for example RGB(0, 0, 0)

LineStyle and Weight cannot both be set to conflicting values — xlDouble ignores Weight, for instance. If a border does not appear as expected, that combination is usually why.

A full grid, not just an outline

For borders on every cell, loop the Borders collection:

Sub GridUsedRange()
    Dim rng As Range
    Set rng = ActiveSheet.UsedRange

    With rng.Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
        .Color = RGB(150, 150, 150)
    End With
End Sub

Setting the whole collection at once covers all six border positions, including the interior vertical and horizontal lines. That is the shortest complete version.

When you want the outline heavier than the inner grid, address the positions individually:

Sub GridWithHeavyOutline()
    Dim rng As Range
    Set rng = ActiveSheet.UsedRange

    With rng.Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With

    rng.BorderAround LineStyle:=xlContinuous, Weight:=xlMedium
End Sub

The individual constants are xlEdgeTop, xlEdgeBottom, xlEdgeLeft, xlEdgeRight, xlInsideVertical and xlInsideHorizontal. Use them like rng.Borders(xlEdgeBottom).Weight = xlThick.

Every sheet in the workbook

Sub GridAllSheets()
    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        If ws.UsedRange.Cells.Count > 1 Then
            With ws.UsedRange.Borders
                .LineStyle = xlContinuous
                .Weight = xlThin
            End With
        End If
    Next ws
End Sub

The Count > 1 check skips empty sheets. On a genuinely empty worksheet, UsedRange still returns cell A1, and bordering a single stray cell looks like a bug to whoever opens the file.

Removing borders again

ActiveSheet.UsedRange.Borders.LineStyle = xlLineStyleNone

One line, and it clears all six positions. Worth keeping next to the macro that adds them — testing border code without an undo is tedious, because macro actions cannot be undone with Ctrl+Z. The same caution applies to any macro that deletes all pictures or charts on a sheet.

The UsedRange trap

UsedRange is not “the cells with data in them”. It is the rectangle Excel currently considers in use, and it grows as soon as a cell is touched — even if you later delete the contents. Formatting alone is enough to extend it, even something as invisible as a custom number format that hides the values in a column.

So a sheet with data in A1:D20 can easily report a used range of A1:Z500 because someone once formatted a column, or pasted and deleted. Your borders then stretch far past the visible data.

Two ways to deal with it. Check what you actually have before running anything:

MsgBox ActiveSheet.UsedRange.Address

Or bypass UsedRange entirely and define the block from the real last row and column:

Sub GridActualData()
    Dim ws As Worksheet, lastRow As Long, lastCol As Long
    Set ws = ActiveSheet

    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column

    With ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastCol)).Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With
End Sub

This walks up from the bottom of column A and left from the end of row 1 to find where the data really stops. It is the more reliable pattern for anything that runs unattended.

If you prefer to keep UsedRange, you can force Excel to recalculate it by deleting the genuinely empty rows and columns below and to the right of your data, then saving and reopening the file. Excel only resets the used range on load.