How to remove borders from cells in a range in Excel using VB.net?
Solution 1
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
Solution 2
Range("A2:K100").Borders.LineStyle = xlNone
Solution 3
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
Solution 4
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!
Yugal Jindle
Everybody is a genius. But if you judge a fish by its ability to climb a tree, it will live its whole life believing that it is stupid. -- Anonymous Github : YugalJindle Twitter : @YugalJindle Google+ : +YugalJindle LinkedIn : http://www.linkedin.com/in/YugalJindle
Updated on January 06, 2020Comments
-
Yugal Jindle over 4 years
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 !