Excel Formatting

**dadrew** · 01-23-2012, 08:31 AM

Hi, Im new to the forum and require a bit of help with a VBA formatting issue.

In my spreadsheet I have a number of columns, the information is exported from Access and works fine except for the fomating issue.

I am trying to put a simple border around every cell in a row. The problem is that some of the cells have data in and some are blenak because the user hasnt filled them in for whatever reason. So in row 2 columns A-F have data in and have borders around them coloumns G - L dont have data in so dont have borders. I should probabley add thet row 1 contains headings and so columns A- L in that row all have data.

Sub CreateBorder(ByRef r As Range)
With r
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    .Borders(xlEdgeLeft).LineStyle = xlNone
    .Borders(xlEdgeTop).LineStyle = xlNone
    .Borders(xlEdgeBottom).LineStyle = xlNone
    .Borders(xlEdgeRight).LineStyle = xlNone
    .Borders(xlInsideVertical).LineStyle = xlNone
    .Borders(xlInsideHorizontal).LineStyle = xlNone
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
    .Borders(xlEdgeLeft).LineStyle = xlNone
    With .Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With .Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
        With .Borders(xlEdgeLeft)
        .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

**arlu1201** · 01-23-2012, 09:00 AM

Welcome to the forum.

What is the issue you are facing with the above code?

**dadrew** · 01-23-2012, 09:09 AM

The issue is that I only get borders around cells with data in them! Some rows have data in every column and the border appears around every cekk which is fine, but if the user omits a bit of data in cell E2 for arguements sake the whole row will have the border except that cell, which llos daft. i want the border to appear arounf every cell in the row up to the last column whatever that may be.

**arlu1201** · 01-25-2012, 06:58 AM

Instead of using the createborder sub, it will be easier if you select the area via code, for eg. range("A2:Z25").....You have not provided the earlier code before the border code. You can find the last row containing data and then assign the borders to it. If you are facing problems, attach a sample file so i can help you.

**snb** · 01-25-2012, 07:18 AM

Sub CreateBorder(ByRef r As Range)
 With r.specialcells(2).Borders
  .LineStyle = xlContinuous
  .Weight = xlThin
  .ColorIndex = xlAutomatic
 End With
End Sub