asked on

Macro to add borders based on last row of data in column A

Hi Experts

I have the following VBA but is not running and or working can you kindly assist...

Dim lastrow as long

LastRow = Range("A1" & Row.Count).End(xlUp).Row
.Range("A1" & LastRow & ":AD" & LastRow).Borders(xlEdgeLeft).LineStyle = xlContinuous
.Range("A1" & LastRow & ":AD" & LastRow).Borders(xlEdgeRight).LineStyle = xlContinuous
.Range("A1" & LastRow & ":AD" & LastRow).Borders(xlEdgeTop).LineStyle = xlContinuous
.Range("A1" & LastRow & ":AD" & LastRow).Borders(xlEdgeBottom).LineStyle = xlContinuos
.Range("A1" & LastRow & ":AD" & LastRow).Borders(xlInsideVertical).LineStyle = xlContinuous
.Range("A1" & LastRow & ":AD" & LastRow).Borders(xlInsideHorizontal).LineStyle = xlContinuous

thanks

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

Hello,

try

Dim lastrow As Long


lastrow = Cells(Rows.Count, "A").End(xlUp).Row
With Range("A" & lastrow & ":AD" & lastrow)
          .Borders(xlEdgeLeft).LineStyle = xlContinuous
          .Borders(xlEdgeRight).LineStyle = xlContinuous
          .Borders(xlEdgeTop).LineStyle = xlContinuous
          .Borders(xlEdgeBottom).LineStyle = xlContinuos
          .Borders(xlInsideVertical).LineStyle = xlContinuous
          .Borders(xlInsideHorizontal).LineStyle = xlContinuous
End With

Open in new window

cheers, teylyn

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

or, this seems to work better:

Sub test()
Dim lastrow As Long
Dim myRange As Range

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set myRange = Range("A" & lastrow & ":AD" & lastrow)

With myRange.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With myRange.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With myRange.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With myRange.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With myRange.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With myRange.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

End Sub

Open in new window

route217

ASKER

Man thanks for the feedback

route217

ASKER

Hi teylyn

I am getting a run error 1004 when using the first vba code you posted..

Thanks

.borders(xlinsidehoruzontal).linestyle = xlcontinuous

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

What version of Excel? The object model is different after 2003. The code I posted will work for 2007 and above.

Please note that XL 2003 has been out of support for more than two years. If you are not using the current XL version, please state your version in your question.

route217

ASKER

Apologies for excel 2003...

ASKER CERTIFIED SOLUTION

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ingeborg Hawighorst (Microsoft MVP / EE MVE)

If you're after formatting only one row, then you can simply delete the last "With" block in the macro posted in comment #37860204. Delete this bit:

    With myRange.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

Open in new window