Macro - Clear Borders

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Finance Guru - 21 Nov 2007 16:21 GMT

Hi All,

I know how to clear the contents of cells in a macro,ie.
Range("A1:B50").ClearContents - however can anyone tell me what needs to be
included in a macro to clear "Borders" created around cells for a given range.

I am just getting into creating macros - so if the above is not clear,please
let me know.
Thank you

Reply to this Message

Gary''s Student - 21 Nov 2007 17:19 GMT

Select a range and:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 11/21/2007 by James Ravenswood
'

'
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
End Sub

Signature

Gary''s Student - gsnu200758

> Hi All,
>
[quoted text clipped - 5 lines]
> let me know.
> Thank you

Reply to this Message

Finance Guru - 22 Nov 2007 08:32 GMT

@ Gary's Student
@ Jim Thomlinson
@ Gord Dibben

Hi Guys - I have tried them out and they work - thankyou all. The diagonals
is a step too far for me,but I will file it away somewhere .. some day to
called upon in the fullness of time.

FG

> Select a range and:
>
[quoted text clipped - 24 lines]
> > let me know.
> > Thank you

Reply to this Message

Jim Thomlinson - 21 Nov 2007 18:15 GMT

Give this a whirl...

sub test
Range("A1:B50").Borders.LineStyle = xlNone
end sub

Signature

HTH...

Jim Thomlinson

> Hi All,
>
[quoted text clipped - 5 lines]
> let me know.
> Thank you

Reply to this Message

Gord Dibben - 21 Nov 2007 19:22 GMT

To account for diagonals revise to this.

Sub test()
With Range("A1:B50")
.Borders.LineStyle = xlNone
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
End With
End Sub

Gord Dibben MS Excel MVP

>Give this a whirl...
>
>sub test
>Range("A1:B50").Borders.LineStyle = xlNone
>end sub

Reply to this Message

Jim Thomlinson - 21 Nov 2007 19:54 GMT

And I just learned something new. I did not realize that my original code did
not remove the diagonals. That being said I can honestly say I have never
used the diagonals... but if I ever do... Thanks Gord

Signature

HTH...

Jim Thomlinson

> To account for diagonals revise to this.
>
[quoted text clipped - 13 lines]
> >Range("A1:B50").Borders.LineStyle = xlNone
> >end sub

Reply to this Message

Gord Dibben - 21 Nov 2007 20:58 GMT

Not only doesn't it remove diagonals, it adds diagonals to cells in the range
that don't originally have them.

Set A1:B10 with cell borders and include diagonals.

Run your original macro.

Clears the outside and interior borders in A1:B10 but leaves the diagonals and
also adds them to A11:B50

Note: the diagonals are actually doubled up so if you go to Format>Cell>Borders
you have to click twice to clear them.

Not nice and definitely weird<g>

Gord

>And I just learned something new. I did not realize that my original code did
>not remove the diagonals. That being said I can honestly say I have never
>used the diagonals... but if I ever do... Thanks Gord

Reply to this Message

Jim Thomlinson - 21 Nov 2007 22:04 GMT

Spookey... This one darn near fits into JWalk's XL oddities.

This has re-affirmed my desire to just never use diagonals. That being said
many moons from now I am going to break down and use them for some obscure
reason and my code will mess them up. On that day I will (vaguely) recall
this conversation with a sense of (told you so)...

Signature

HTH...

Jim Thomlinson

> Not only doesn't it remove diagonals, it adds diagonals to cells in the range
> that don't originally have them.
[quoted text clipped - 16 lines]
> >not remove the diagonals. That being said I can honestly say I have never
> >used the diagonals... but if I ever do... Thanks Gord

Reply to this Message