Clear BorderAround in VBA

  • Thread starter Thread starter EagleOne
  • Start date Start date
E

EagleOne

2003

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


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

With Worksheets("Sheet1").Range("B1:D1")
.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
 
Hi Dennis

Try
Worksheets("Sheet1").Range("B1:D1").BorderAround
LineStyle:=xlLineStyleNone
 
Hi

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

Worksheets("Sheet1").Range("B1:D1").BorderAround _
LineStyle:=xlLineStyleNone
 
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,

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
 
Sub noborders()
Worksheets("Sheet1").Range("B1:D1").Borders.LineStyle = xlNone
End Sub
 
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!!
 
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
 
Jon,

I ended up using the "verbose" method:

With Worksheets("Sheet1").Range("B1:D1")
.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("B1:D1").Borders.LineStyle = xlNone
End Sub
 
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
 
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("A1:D4").BorderAround _
ColorIndex:=3, Weight:=xlThick
End Sub

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

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

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

It "should" work but does not in 2003.

Thanks again!
 
Hi Dennis

In my last posting, I confirmed that Don's solution
Sub noborders()'remove all
Worksheets("Sheet1").Range("A1:D4") _
..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.
 
Jon, I'm only glad I'm able to contribute a small percentage of what you
have done, especially in charting.
 
Back
Top