Clear BorderAround in VBA

[EagleOne]

2003

VBA offers a shortcut to add borders around a range like:
Worksheets("Sheet1").Range("B11").BorderAround ColorIndex:=3, _
Weight:=xlThick


But to remove the border it appears that only the following works:

With Worksheets("Sheet1").Range("B11")
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
Endwith

The VBA documention mentions:
"To clear the border, you must set the LineStyle property to
xlLineStyleNone for all the cells in the range."

But that does not work as stated. In fact, it errors out re: Object.

Is there a shortcut BorderAround Clear or is the 8 step process above
the only way?

TIA

Dennis

 

[Roger Govier]

Hi Dennis

Try
Worksheets("Sheet1").Range("B11").BorderAround
LineStyle:=xlLineStyleNone

 

[Roger Govier]

Hi

The posting wrapped the line. It should be all on one line or given the
normal VBA line break.

Worksheets("Sheet1").Range("B11").BorderAround _
LineStyle:=xlLineStyleNone

 

[EagleOne]

Bob,
Thinking that I may have made a bad call, I did try your suggestion, as
I had done on my own, as per my original post.

Unfortunately, even though this is in the documentation it does NOT
work.

Thanks for your time!

Dennis

 

[Roger Govier]

Hi Dennis

Bob, ???

Not sure whether you were replying to my post, but my suggestion
definitely works in XL2003

 

[EagleOne]

Roger,

Well, I have an issue here then. Probably, something is too obvious
for me to see it.

On this 2003 installation, than line of Code does not work in the
Regular module or in the Immediate Window.

Curses!

Thanks Roger

 

[Don Guillett]

Sub noborders()
Worksheets("Sheet1").Range("B11").Borders.LineStyle = xlNone
End Sub

 

[Roger Govier]

Hi Dennis

My apologies.
I had been playing with a combination of
..BorderAround Weight:=xlHairline and
..BorderAround LineStyle:=xlLineStyleNone

and my eyesight was not sharp enough to pick up that the hairline still
left a Border.
I thought the LineStyle had sorted it, so omitted any reference to
Weight when posting.
The method clearly does not work and I apologise for the confusion.

Luckily, Don has come up with the solution which does work.
..Borders.LineStyle = xlNone

Many thanks Don!!

 

[Don Guillett]

Glad to help.

 

[Jon Peltier]

Dennis -

Did this work for you? It doesn't work for me in XL2003. I tried for five
minutes on my own before reading the rest of the thread, then saw that all
of the proposed solutions were ones I'd discarded.

- Jon

 

[EagleOne]

Jon,

I ended up using the "verbose" method:

With Worksheets("Sheet1").Range("B11")
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlNone
.Borders(xlEdgeTop).LineStyle = xlNone
.Borders(xlEdgeBottom).LineStyle = xlNone
.Borders(xlEdgeRight).LineStyle = xlNone
.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
Endwith

Then Don stepped in as usual with the shortcut method
Thanks all!

EagleOne


Sub noborders()
Worksheets("Sheet1").Range("B11").Borders.LineStyle = xlNone
End Sub

 

[EagleOne]

Don,

I so much appreciate your knowledge and time.

Unfortunately also does not work when the application of borders was via Borderaround

I fairness the code I used is below: (Probably, I am doing something wrong)

For Each MyCell In myRange
If MyCell.Borders(xlEdgeLeft).ColorIndex = 3 Then
'MyCell.BorderAround LineStyle:=xlLineStyleNone DOES NOT WORK!!
'MyCell.Borders.LineStyle = xlNone 'DOES NOT WORK!!
MyCell.Borders(xlDiagonalDown).LineStyle = xlNone
MyCell.Borders(xlDiagonalUp).LineStyle = xlNone
MyCell.Borders(xlEdgeLeft).LineStyle = xlNone
MyCell.Borders(xlEdgeTop).LineStyle = xlNone
MyCell.Borders(xlEdgeBottom).LineStyle = xlNone
MyCell.Borders(xlEdgeRight).LineStyle = xlNone
MyCell.Borders(xlInsideVertical).LineStyle = xlNone
MyCell.Borders(xlInsideHorizontal).LineStyle = xlNone
End If
Next

EagleOne

 

[Don Guillett]

xl2002.all updates
I just successfully re-tested both of these. The first created ONLY an
outside border. The 2nd
one removed ALL borders. Is this what you want?

Sub bordersaround()
Worksheets("Sheet1").Range("A14").BorderAround _
ColorIndex:=3, Weight:=xlThick
End Sub

Sub noborders()'remove all
Worksheets("Sheet1").Range("A14") _
.Borders.LineStyle = xlNone

End Sub
this will put in interior borders.
Worksheets(1).Range("A1:d4").Borders.LineStyle = xlContinuous

 

[EagleOne]

Don,

I believe that the issue is with XL 2003 according to previous posts.

It "should" work but does not in 2003.

Thanks again!

 

[Jon Peltier]

Hi Don -

Worksheets("Sheet1").Range("A14") _
.Borders.LineStyle = xlNone

Duh, I forgot that one.

- Jon

 

[Roger Govier]

Hi Dennis

In my last posting, I confirmed that Don's solution
Sub noborders()'remove all
Worksheets("Sheet1").Range("A14") _
..Borders.LineStyle = xlNone

did work, and I am using XL2003.

I agree that
..BorderAround LineStyle:=xlLineStyleNone
does not work in XL2003, contrary to my first thought.

 

[Don Guillett]

Jon, I'm only glad I'm able to contribute a small percentage of what you
have done, especially in charting.