Help condensing snippet of simple code

[JamesonMH]

Hi all,

As noted on earlier post this week, I'm just starting out with VBA. When I used the recorder to remove all border lines it spits this code below. Seems really long? I want to learn the smartest/most concise code I can from the get go so I don't start with bad habits.

Range("I7:N26").Select
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

I tried using the "With" approach a few different ways to shorten it (adding With/End With and removing all instances of "Selection"), but not sure what the rules/order are... I'm keep getting an error msg that I need an object or something similar.

Any help would be appreciated.

Thanks,
James

 

[ISY]

Hi

One way...

Sub test()
  With ActiveSheet.Range("I7:N26")
    .Borders.LineStyle = xlNone
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
  End With
End Sub

 

[JamesonMH]

Hi

One way...

Sub test()
  With ActiveSheet.Range("I7:N26")
    .Borders.LineStyle = xlNone
    .Borders(xlDiagonalDown).LineStyle = xlNone
    .Borders(xlDiagonalUp).LineStyle = xlNone
  End With
End Sub

That's a lot cleaner! How come on the first 3rd row there isn't a bracket (xl...), but the next 2 rows use the bracket? What does that 3rd row accomplish when used like that?

And also, what made you choose just the xlDiagonalUp and xlDiagonalDown and exclude all of the others that were in my original?

Thanks ISY.

James

 

[MARK858]

.Borders.LineStyle refers to the Borders collection but when referring to a range or style only includes the 4 main Index types (left, right, top, and bottom)

.Borders(xlDiagonalDown).LineStyle with the brackets is using the Index of the individual properties of the collection. For some reason the 2 diagonals aren't included in the collection when using range or style so you have to deal with them individually to clear all borders.

 

[JamesonMH]

Thanks Mark. Those rules are a bit confusing but I'll follow what you noted going forward.

So if it were you, would you revise/shorten my original code just as ISY did, to accomplish no border lines whatsoever in the range?

 

[MARK858]

So if it were you, would you revise/shorten my original code just as ISY did, to accomplish no border lines whatsoever in the range?

It is how I would do it but speed wise there is no real gain, having said that I can't remember the last time I used diagonal borders and so in that case

Range("I7:N26").Borders.LineStyle = xlNone

suffices.

 

[JamesonMH]

It is how I would do it but speed wise there is no real gain, having said that I can't remember the last time I used diagonal borders and so in that case

Range("I7:N26").Borders.LineStyle = xlNone

suffices.

That's great Mark, I'll go with that cleaner approach. One line is much better than what I started with here.

Thanks again Mark and ISY for your help.

James