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
4 comentários:
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.
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
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.
The code has already a sub...
Post a Comment