Borders without selection

[tourless]

Hi Folks.

I'm betting there is a more efficient way do to this... possibly without having to select the cell(s)?

Range("A22:J22").Select
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
    
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With

 

[Z51]

You could probably use:

Sub tourless()
Dim i As Long

'Clear borders
Range("A22:J22").Borders.LineStyle = xlNone

'Add Edge borders
For i = 7 To 10
    With Range("A22:J22").Borders(i)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
Next i
End Sub

XlBordersIndex enumeration (Excel)

Office VBA reference topic

learn.microsoft.com

 

[tourless]

You could probably use:

Sub tourless()
Dim i As Long

'Clear borders
Range("A22:J22").Borders.LineStyle = xlNone

'Add Edge borders
For i = 7 To 10
    With Range("A22:J22").Borders(i)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlMedium
    End With
Next i
End Sub

XlBordersIndex enumeration (Excel)

Office VBA reference topic

learn.microsoft.com

Well that worked like a charm! One question for my own knowledge... why do you use 5-12 to remove then 7-10 to add?

 

[Z51]

I actually adjusted the code... the 5-12 are all of the individual border index values (see the link to the Microsoft page I posted). I looped through them to clear them, but then realized a loop wasn't necessary to remove all borders. The loop from 7-10 is needed to set the individual Left, Right, Top, Bottom edges - as those index values correlate to the edges mentioned.

 

[tourless]

I actually adjusted the code... the 5-12 are all of the individual border index values (see the link to the Microsoft page I posted). I looped through them to clear them, but then realized a loop wasn't necessary to remove all borders. The loop from 7-10 is needed to set the individual Left, Right, Top, Bottom edges - as those index values correlate to the edges mentioned.

I noticed that adjustment and that's good to know. I never knew about those index values. Thank you.

 

[Peter_SSs]

What about adding all the borders at once?

Sub tourless_v2()
  Range("A22:J22").Borders.LineStyle = xlNone
  Range("A22:J22").BorderAround LineStyle:=xlContinuous, Weight:=xlMedium, ColorIndex:=0
End Sub

 

[tourless]

Range("A22:J22").BorderAround LineStyle:=xlContinuous, Weight:=xlMedium, ColorIndex:=0

That's nice and tight. I like that. Thank you everyone!

 

[Peter_SSs]

You're welcome.