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.

'\\ 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.

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

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