Removes all borders for the selected cells : Cell Format « Excel « VBA / Excel / Access / Word






  1. VBA / Excel / Access / Word
  2. Excel
  3. Cell Format

Removes all borders for the selected cells

 

Sub RemoveAllBorders()
  Dim calcModus&, updateModus&, i
  Dim rng As Range, ar As Range
  Dim brd As Border
  If Selection Is Nothing Then Exit Sub
  
  calcModus = Application.Calculation
  updateModus = Application.ScreenUpdating
  Application.Calculation = xlManual
  Application.ScreenUpdating = False
  For Each ar In Selection.Areas   
    For Each rng In ar             
      For Each i In Array(xlEdgeTop, xlEdgeBottom, xlEdgeLeft, xlEdgeRight, xlDiagonalDown, xlDiagonalUp)
        rng.Borders(i).LineStyle = xlLineStyleNone
      Next i
      If rng.Column > 1 Then
        rng.Offset(0, -1).Borders(xlRight).LineStyle = xlLineStyleNone
      End If
      If rng.Column < 256 Then
         rng.Offset(0, 1).Borders(xlLeft).LineStyle = xlLineStyleNone
      End If
      If rng.Row > 1 Then
        rng.Offset(-1, 0).Borders(xlBottom).LineStyle = xlLineStyleNone
      End If
      If rng.Row < 65536 Then
         rng.Offset(1, 0).Borders(xlTop).LineStyle = xlLineStyleNone
      End If
    Next rng
  Next ar
  Application.Calculation = calcModus
  Application.ScreenUpdating = updateModus
End Sub








Related examples in the same category

1.the font color of all cells in the active worksheet is set to red
2.Sets just the color of cell C1 to red.
3.Set cell color
4.Make the font in number cell bold
5.Set data to cell D1 of the selected worksheet. And format its contents with color and borders.
6.Color cells
7.Make a Cell font bold based on the cell value
8.Coloring all negative cells' backgrounds red
9.Color multiple-column ranges
10.Makes cell background red if the value is negative