create borders using vba

Question

hi all,

using macro, i want to create borders from col A - col Y, and for rows it depend with last cell in col A.

i.e.

if col A have values till A1300, then it will create borders from A1:Y1300.

if col A have values till A50, then it will create borders from A1:Y50.

hope you get with i want

many thanks

---

Regards, Rasyha

Answer 1

If the requirment is for Borderaround; that could be acheived using BorderAround

With Range("A1", Cells(Cells(Rows.Count, "A").End(xlUp).Row, "Y"))

  .Borders.LineStyle = xlNone

  .BorderAround xlContinuous, xlThin, xlColorIndexAutomatic

End With

Answer 2

If the region around which you want to put the border contains no blank rows or blank columns but column Z is blank then

With Range("A1").CurrentRegion

  .Borders.LineStyle = xlNone  ' clear current borders 

  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

End With

---

if the assumptions I made are not valid then replace the first line with

  With Range("A1",Cells(Cells(Rows.Count,"A").End(xlUp).Row, "Y"))

---

Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk

Answer 3

Nice one, Jackpot.

I forgot that useful BorderAround method.

Much more succinct than the code the macro recorder gave me!

---

Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk