Borders

[ddrew]

I have a funtion on an Access Form that exports my dta to an excel spreadsheet.

When the spreadsheet opens I have some functions built in for automatic formating. The last thing I need to do is create borders around the the cells.

Ive played around with Borders and BorderAround but cant figure it out!

In addition I know how many columns there are but nt how many rows of course!

 

[Kiwiman]

Howzit

Columns in Sheet - this assumes the header row or first data row is row 1. This will return the last column used.

Dim lngLastCOl as long

lngLastCol = Sheets("Your sheet).Cells(1,Columns.Count).End(xlToLeft).Column

For the borders, it may be best to create a macro in Excel to see the syntax then modify accordingly. But as an example:

Range("B5:G5").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThick
    End With