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

route217
route217 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Awarded 2010

Commented:
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
Most Valuable Expert 2011
Awarded 2010

Commented:
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

route217Junior

Author

Commented:
Man thanks for the feedback
route217Junior

Author

Commented:
Hi teylyn

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

Thanks

.borders(xlinsidehoruzontal).linestyle = xlcontinuous
Most Valuable Expert 2011
Awarded 2010

Commented:
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.
route217Junior

Author

Commented:
Apologies for excel 2003...
Most Valuable Expert 2011
Awarded 2010
Commented:
This one will work in Excel 2003

Sub BorderLine()

lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set myRange = Range("A" & lastrow & ":AD" & lastrow)
    With myRange.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With myRange.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With myRange.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With myRange.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With myRange.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With

End Sub

Open in new window

Most Valuable Expert 2011
Awarded 2010

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial