Remove Cell Borders With Macro

  • Hi,


    I'm still playing around and trying to explore the VBA ocean :cry:


    The code below is self explanatory but it does not undo the border related formatting. Can you please guide and help me figure out my mistake. The attached image shows the worksheet that I am using.



    Thanks.

  • Re: Formatting Of Cell Borders


    Thanks for the reply Dave. However there's got to be solution without having to copy/paste the resulting code from the macro recorder. I have read that the code automatically generated by the macro recorder is not the most efficient one.


    Since I have just started learning VBA, therefore I am trying by best to follow "best-practices" from the very start... Given your expertise, I hope that you'll give me a pat on the back for being on the right track :rolleyes: :roll eyes: :unsure:

  • Re: Formatting Of Cell Borders


    The macro recorder is an excellent learning tool. It gives you a base to start from and code can often be trimmed down, particually Select, Selection, Active and Activate code.

  • Re: Remove Cell Borders With Macro


    The code works for me.
    Is the colour of the cell set using conditional formatting?


    I agree with Dave. The macro recorder, in versions before xl2007, is a great tool.
    You do have to optimize the code it produces but it's a lot quicker than searching for an answer in books or forums. Especially if you do not know where to start with the syntax.

  • Re: Remove Cell Borders With Macro


    Thanks Dave and Andy for your recommendation regarding using the macro recorder as a learning point....


    Andy: No, I have not used any conditional formatting. I have manually and randomly set the color of the cells (in the image that I attached). I'm surprised that the code is working for you :confused:

  • Re: Remove Cell Borders With Macro


    You might be suffering from an Excel Gotcha; see
    http://tinyurl.com/yua6xr


    I had no problems activating or selecting my cell by just uncommenting either of those lines.


    However, the line
    .BorderAround LineStyle:=xlContinuous, Weight:=xlThin, ColorIndex:=xlColorIndexAutomatic
    (which has a doubtful line continuation character btw) wouldn't undo your borders, only change it from dashed to continuous. Default is no borders.


    p45cal

  • Re: Remove Cell Borders With Macro


    To remove the borders use this instead of the BorderAround method, which does not seem to work when removing borders.


    [vba] .Borders.LineStyle = xlLineStyleNone [/vba]

  • Re: Remove Cell Borders With Macro


    Quote from Andy Pope

    To remove the borders use this instead of the BorderAround method, which does not seem to work when removing borders.


    [vba] .Borders.LineStyle = xlLineStyleNone [/vba]


    Hi Andy,
    Thanks for the quick feedback. Can you please have a look at the code in the attached file. It's almost similar to the earlier file. There are 2 points that have totally confused me. You'll see these 2 in the form of comments in the code.


    Can you kindly help and guide.


    Thanks.

  • Re: Remove Cell Borders With Macro


    Read Dave's post about starting new questions in an existing thread.


    And when you post you new question I will post an answer telling you not to turn screen updating off if you want to see the active selection change.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!