VBA - Put borders around all used cells on a sheet

This code is used to put borders around every used cell on a sheet.

Sub DrawBorders()
   With Cells.SpecialCells(xlCellTypeConstants, 23)
       .BorderAround xlContinuous, xlThin, xlColorIndexAutomatic
        On Error Resume Next 'used in case there are no inside borders
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .Weight = xlThin
            .ColorIndex = xlAutomatic
        End With
    End With
End Sub

5 comentários:

Ashish said...

Hi Your script is great..
I like to have a little enhancement in this, want i like to have this i like to put borders in the whole range used. Like in a ROW if we found any one value non-blank and non-null then create borders in that full row.
Is this possible.

Nick said...

Ashish,

a Slightly easier method that does what you need:

Public Sub addBorders(SheetName)

Dim rowTcha As Integer
Dim wsSheetName As Excel.Worksheet
' Uses the Excel.Worksheet variable type to define a short name for a sheet (Saves using "ActiveWorkbook.Sheets("SheetName")." when making changes)
Set wsSheetToChange = ActiveWorkbook.Sheets(SheetName)
' Identifies how many rows contain any data in any column
rowTcha = wsSheetToChange.UsedRange.rows.Count

' Adds a boarder around the outside of the range of cells
wsSheetToChange.Range("A1:Q" & rowTcha & "").BorderAround xlContinuous
' Adds the boarders around the cells inside of the range
wsSheetToChange.Range("A1:Q" & rowTcha & "").Borders(xlInsideHorizontal).LineStyle = xlContinuous
wsSheetToChange.Range("A1:Q" & rowTcha & "").Borders(xlInsideVertical).LineStyle = xlContinuous

End Sub

Anonymous said...

Hmmm... I get a runtime error 1004: application-defined or object-defined error warning with this code. Any ideas what may be causing that please? I copied it directly into a sub, unchanged.

jppinto said...

The code has already a sub...

katja said...

It's a pity that you haven't visualize anything. Without pictures it's hard for me to understand it. So I watched a video of a workshop, that I found on http://www.excel-aid.com/excel-borders-drawing-borders-around-cells-and-cell-ranges.html