PDA

View Full Version : Can this basic code be refined?



starsky
07-02-2009, 09:04 AM
Hello,

I wondered if there was a way to trim this code for formatting cells to save space.

Range("A10:P10").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

Many thanks.

JKwan
07-02-2009, 09:18 AM
Public Enum XlBordersIndex
xlDiagonalDown = 5
xlDiagonalUp = 6
xlEdgeBottom = 9
xlEdgeLeft = 7
xlEdgeRight = 10
xlEdgeTop = 8
xlInsideHorizontal = 12
xlInsideVertical = 11
End Enum

you can step it thru using a loop.

mdmackillop
07-02-2009, 09:40 AM
Option Explicit
Sub Borders1()
Dim Rng As Range
Set Rng = Range("A10:P10")
Range(Rng, Rng.End(xlDown)).Borders.LineStyle = xlContinuous
End Sub

'or maybe

Sub Borders2()
Range("A10").CurrentRegion.Borders.LineStyle = xlContinuous
End Sub

Bob Phillips
07-02-2009, 09:42 AM
With Range(Range("A10:P10"), Range("A10:P10").End(xlDown))

.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlInsideVertical).Weight = xlThin
.Borders(xlInsideHorizontal).Weight = xlThin
End With

starsky
07-02-2009, 12:20 PM
Wow, thanks guys. Will work on them tomorow.