CODEWITHSUNDEEP
.netvb.netexcelbordervba
Yugal Jindle
Yugal Jindle

Reputation: 45646

How to remove borders from cells in a range in Excel using VB.net?

Aim to Achieve: To get rid of borders if any in the cells of range.

I have :

Dim range As Excel.Range = sheet.Range("A2:K100")
For Each cell In range
    // Some cells in the Range has borders
    // How to remove borders from cells in the range
Next cell

Please help.. !

I am new to Vb.net !

Upvotes: 19

Views: 133354

Answers (4)

Unknown
Unknown

Reputation: 313

Range("A2:K100").Borders.LineStyle = xlNone

Upvotes: 14

Steven Martin
Steven Martin

Reputation: 3272

why are all the answers so convoluted?

for the entire sheet use...

With .Cells
       .Borders.LineStyle = xlLineStyleNone
End With

for a range just replace .Cells as appropriate

Upvotes: 6

VVS
VVS

Reputation: 19604

range.Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlLineStyleNone
range.Borders(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlLineStyleNone
range.Borders(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlLineStyleNone
range.Borders(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlLineStyleNone
range.Borders(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlLineStyleNone
range.Borders(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlLineStyleNone

Removes the borders around the cells and between cells (via xlInsideHorizontal and xlInsideVertical). If you expect diagonal borders, include xlDiagonalDown and xlDiagonalUp.

Okay, the above code was very verbose. The following should do it too:

For Each border in range.Borders
    border.LineStyle = Excel.XlLineStyle.xlLineStyleNone
Next

See: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.borders.aspx

EDIT:

While looking over the MSDN page, I'm wondering if this one liner could do it too:

range.Borders.LineStyle = Excel.XlLineStyle.xlLineStyleNone

Upvotes: 32

Lucian
Lucian

Reputation: 3554

Check NamedRange.BorderAround Method .

Dim range As Excel.Range = sheet.Range("A2:K100")
range.BorderAround(Excel.XlLineStyle.xlLineStyleNone, Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, missing)

cheers and good luck!

Upvotes: 1

Related Questions