help needed with borders

[kardifflad]

Hello. I am hoping someone can help me with this little problem. I hav
this bit of code that tidies up my spreadsheet for me. It makes all th
font the same, autofits the columns and then adds borders around all th
data. It seems to work ok unless theres only one row of data under m
headings "(which are in rows 1, 2 and 3). If there's only one row o
data then the code puts borders right the way doing the entir
spreadsheet. it should only go around the rows with data.

'\\ Select everything
Cells.Select

'\\ Autofit
' Cells.EntireColumn.AutoFit

'\\ Set Font and Style
With Selection.Font
.Name = "Arial"
.Size = 11
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

'\\ Cell Alignment
Columns("A:F").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom

End With

'\\ Add borders to info below header rows
Range("A4").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).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


thank you

 

[Claus Busch]

Hi,

Am Mon, 26 Nov 2012 17:53:45 +0000 schrieb kardifflad:

Hello. I am hoping someone can help me with this little problem. I have
this bit of code that tidies up my spreadsheet for me. It makes all the
font the same, autofits the columns and then adds borders around all the
data. It seems to work ok unless theres only one row of data under my
headings "(which are in rows 1, 2 and 3). If there's only one row of
data then the code puts borders right the way doing the entire
spreadsheet. it should only go around the rows with data.

try:

Sub Test()
Dim LRow As Long
Dim myRange As Range
Dim BorderRange As Range

LRow = Cells(Rows.Count, 1).End(xlUp).Row
Set myRange = Range("A1:F" & LRow)
Set BorderRange = Range("A4:F" & LRow)

With myRange
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom

With .Font
.Name = "Arial"
.Size = 11
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
End With

With BorderRange
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With

With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With

End Sub


Regards
Claus Busch

 

[kardifflad]

that worked absolutely perfectly. thank you very much for your kin
assistance. Much appreciated