How to use loop to delete border from empty cells

[Ahmed786]

Hello dear,
My code is to remove border from empty cell which is working fine.
Dim ismycellempty As Boolean
ismycellempty = IsEmpty(Range("BG11"))
If ismycellempty = True Then
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Else
MsgBox ("no cell empty")
End If
How can i use same code in loop. I wish to use it from Range "BG11 to BG49"

 

[HaHoBe]

Hi Ahmend786,

please use code-tags in the future to post your procedures like described in How to Post Your VBA Code.

Maybe

Public Sub MrE_1230342_1703212()
' https://www.mrexcel.com/board/threads/how-to-use-loop-to-delete-border-from-empty-cells.1230342/

Dim blnEmpty As Boolean
Dim rngCell As Range
Dim positions As Variant
Dim lngCnt As Long

positions = Array(xlDiagonalDown, xlDiagonalUp, xlDiagonalDown, xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight, xlInsideVertical, xlInsideHorizontal)

For Each rngCell In Range("BG11:BG49")
  blnEmpty = IsEmpty(rngCell)
  If blnEmpty Then
    For lngCnt = LBound(positions) To UBound(positions)
      rngCell.Borders(positions(lngCnt)).LineStyle = xlNone
    Next lngCnt
  Else
    MsgBox rngCell.Address(0, 0) & "is not empty", vbInformation, "No empty cell"
  End If
Next rngCell
End Sub

Ciao,
Holger

 

[Ahmed786]

Highly appreciated thank u very much

 

[johnnyL]

A short but sweet method:

    If WorksheetFunction.CountBlank(Range("BG11:BG49")) > 0 Then
        Range("BG11:BG49").SpecialCells(xlCellTypeBlanks).Borders.LineStyle = xlNone
    Else
        MsgBox ("no cell empty")
    End If

 

[HaHoBe]

Hi johnnyL,

nice one but if in the cells the xlDiagonalDown and xlDiagonalUp remained - any tip on how to get rid of these as well?

Holger

 

[johnnyL]

Hi johnnyL,

nice one but if in the cells the xlDiagonalDown and xlDiagonalUp remained - any tip on how to get rid of these as well?

Holger

Hmm. I thought the line of code I provided removed all of the borders. After testing, I see the diagonals remain.

I guess you would have to add the additional 2 lines of code to remove those diagonals if they are a possibility of existing.

 

[MARK858]

I guess you would have to add the additional 2 lines of code to remove those diagonals if they are a possibility of existing.

Correct

Hmm. I thought the line of code I provided removed all of the borders. After testing, I see the diagonals remain.

From Borders object (Excel)

"A collection of four Border objects that represent the four borders of a Range object or Style object."

"Use the Borders property to return the Borders collection, which contains all four borders. You can apply different borders to each side of a cell or range."

and from Border object (Excel)

"Range and Style objects have four discrete borders—left, right, top, and bottom—which can be returned individually or as a group. Use the Borders property to return the Borders collection, which contains all four borders and treats the borders as a unit."

I've never seen MS state a reason why diagonals aren't included (or specifically state that they aren't for that matter)