CODEWITHSUNDEEP
vbaexcel
Strange Tamer
Strange Tamer

Reputation: 39

Add the Top border if the the cell is not empty

This is adding the top border. 

Sub getBorders()
Dim rngToChange As Range
Dim C As Range
Set rngToChange = ActiveSheet.Range("B6:C10")

For Each C In rngToChange
    If C <> "" Then
        C.Borders(xlEdgeTop).LineStyle = (xlContinuous)
        C.Borders.Weight = xlThin
        C.Borders.ColorIndex = xlAutomatic
    Else
        C.Borders(xlEdgeTop).LineStyle = xlNone

    End If

Next
End Sub

However, in the last row, the bottom border is deleted. How to modify the loop?

enter image description here

Upvotes: 0

Views: 1118

Answers (2)

vanathaiyan
vanathaiyan

Reputation: 1070

you can try this also

  Sub getBorders()
     Dim rngToChange As Range
     Dim C As Range
     Set rngToChange = ActiveSheet.Range("B6:C10")

    For Each C In rngToChange
       If C <> "" Then
        C.Borders(xlEdgeTop).LineStyle = (xlContinuous)
        C.Borders.Weight = xlThin
        C.Borders.ColorIndex = xlAutomatic
      Else
     If C = "B10" Or C = "C10" Then
       Else
        C.Borders(xlEdgeLeft).LineStyle = (xlContinuous)
        C.Borders(xlEdgeRight).LineStyle = (xlContinuous)
        C.Borders(xlEdgeTop).LineStyle = xlNone
     End If
End If

 Next
 End Sub

Upvotes: 0

Jordan
Jordan

Reputation: 4514

You could check if 'C' is in the last row and then apply a bottom border if the condition is met:

Sub getBorders()
Dim rngToChange As Range
Dim C As Range
Set rngToChange = ActiveSheet.Range("B6:C10")

For Each C In rngToChange
    If C <> "" Then
        C.Borders(xlEdgeTop).LineStyle = (xlContinuous)
        C.Borders.Weight = xlThin
        C.Borders.ColorIndex = xlAutomatic
    Else
        C.Borders(xlEdgeTop).LineStyle = xlNone
    End If
    'If you always know the end of your range simply replace 10 with the end row
    If C.Row = 10 Then  
        C.Borders(xlEdgeBottom).LineStyle = (xlContinuous)
        C.Borders.Weight = xlThin
        C.Borders.ColorIndex = xlAutomatic
    End if
Next
End Sub

Alternatively you could replace the 10 with something like ActiveSheet.Cells(Rows.Count, "B").End(xlup).Row if you don't know where the range ends but want to select the last non-empty cell in column B.

Upvotes: 1

Related Questions