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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2