|
Clear BorderAround in VBA
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 Object. Is there a shortcut BorderAround Clear or is the 8 step process above the only way? TIA Dennis |
Clear BorderAround in VBA
Hi Dennis
Try Worksheets("Sheet1").Range("B1:D1").BorderAround LineStyle:=xlLineStyleNone -- Regards Roger Govier "EagleOne" wrote in message ups.com... 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 Object. Is there a shortcut BorderAround Clear or is the 8 step process above the only way? TIA Dennis |
Clear BorderAround in VBA
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 -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Dennis Try Worksheets("Sheet1").Range("B1:D1").BorderAround LineStyle:=xlLineStyleNone -- Regards Roger Govier "EagleOne" wrote in message ups.com... 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 Object. Is there a shortcut BorderAround Clear or is the 8 step process above the only way? TIA Dennis |
Clear BorderAround in VBA
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 wrote: Hi Dennis Try Worksheets("Sheet1").Range("B1:D1").BorderAround LineStyle:=xlLineStyleNone -- Regards Roger Govier "EagleOne" wrote in message ups.com... 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 Object. Is there a shortcut BorderAround Clear or is the 8 step process above the only way? TIA Dennis |
Clear BorderAround in VBA
Hi Dennis
Bob, ??? Not sure whether you were replying to my post, but my suggestion definitely works in XL2003 -- Regards Roger Govier "EagleOne" wrote in message oups.com... 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 wrote: Hi Dennis Try Worksheets("Sheet1").Range("B1:D1").BorderAround LineStyle:=xlLineStyleNone -- Regards Roger Govier "EagleOne" wrote in message ups.com... 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 Object. Is there a shortcut BorderAround Clear or is the 8 step process above the only way? TIA Dennis |
Clear BorderAround in VBA
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 Roger Govier wrote: Hi Dennis Bob, ??? Not sure whether you were replying to my post, but my suggestion definitely works in XL2003 -- Regards Roger Govier "EagleOne" wrote in message oups.com... 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 wrote: Hi Dennis Try Worksheets("Sheet1").Range("B1:D1").BorderAround LineStyle:=xlLineStyleNone -- Regards Roger Govier "EagleOne" wrote in message ups.com... 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 Object. Is there a shortcut BorderAround Clear or is the 8 step process above the only way? TIA Dennis |
Clear BorderAround in VBA
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!! -- Regards Roger Govier "EagleOne" wrote in message ups.com... 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 Roger Govier wrote: Hi Dennis Bob, ??? Not sure whether you were replying to my post, but my suggestion definitely works in XL2003 -- Regards Roger Govier "EagleOne" wrote in message oups.com... 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 wrote: Hi Dennis Try Worksheets("Sheet1").Range("B1:D1").BorderAround LineStyle:=xlLineStyleNone -- Regards Roger Govier "EagleOne" wrote in message ups.com... 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 Object. Is there a shortcut BorderAround Clear or is the 8 step process above the only way? TIA Dennis |
Clear BorderAround in VBA
|
Clear BorderAround in VBA
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 Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Don Guillett" wrote in message ... Sub noborders() Worksheets("Sheet1").Range("B1:D1").Borders.LineSt yle = xlNone End Sub -- Don Guillett SalesAid Software "EagleOne" wrote in message ups.com... 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 Object. Is there a shortcut BorderAround Clear or is the 8 step process above the only way? TIA Dennis |
Clear BorderAround in VBA
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.LineSt yle = xlNone End Sub "Jon Peltier" wrote: 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 Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Don Guillett" wrote in message ... Sub noborders() Worksheets("Sheet1").Range("B1:D1").Borders.LineSt yle = xlNone End Sub -- Don Guillett SalesAid Software "EagleOne" wrote in message ups.com... 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 Object. Is there a shortcut BorderAround Clear or is the 8 step process above the only way? TIA Dennis |
Clear BorderAround in VBA
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" wrote: Don Guillett |
Clear BorderAround in VBA
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 Guillett SalesAid Software wrote in message ... 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" wrote: Don Guillett |
Clear BorderAround in VBA
Don,
I believe that the issue is with XL 2003 according to previous posts. It "should" work but does not in 2003. Thanks again! "Don Guillett" wrote: 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").BorderAroun d _ 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 |
Clear BorderAround in VBA
Hi Don -
Worksheets("Sheet1").Range("A1:D4") _ .Borders.LineStyle = xlNone Duh, I forgot that one. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Don Guillett" wrote in message ... 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 Guillett SalesAid Software wrote in message ... 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" wrote: Don Guillett |
Clear BorderAround in VBA
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. -- Regards Roger Govier wrote in message ... Don, I believe that the issue is with XL 2003 according to previous posts. It "should" work but does not in 2003. Thanks again! "Don Guillett" wrote: 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").BorderArou nd _ 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 |
Clear BorderAround in VBA
Jon, I'm only glad I'm able to contribute a small percentage of what you
have done, especially in charting. -- Don Guillett SalesAid Software "Jon Peltier" wrote in message ... Hi Don - Worksheets("Sheet1").Range("A1:D4") _ .Borders.LineStyle = xlNone Duh, I forgot that one. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Don Guillett" wrote in message ... 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 Guillett SalesAid Software wrote in message ... 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" wrote: Don Guillett |
| All times are GMT +1. The time now is 03:04 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com