+ Reply to Thread
Results 1 to 11 of 11

Dynamically add Border to desired range

  1. #1
    Registered User
    Join Date
    04-16-2012
    Location
    Space
    MS-Off Ver
    Excel 2010
    Posts
    14

    Dynamically add Border to desired range

    Hi all,
    I have groups of data separated by a blank row. How can I dynamically add a border to each group?
    I'm not sure how to select the cell at the beginning of the group and then select the last cell in the desired rage, then turn the selection into a rage.
    Once I have the range I can add the border.

    Thanks,


    Please Login or Register  to view this content.
    Last edited by AR-51; 03-15-2013 at 12:38 AM.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Dynamically add Border to desired range

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code in [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    One possible way
    Please Login or Register  to view this content.
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    04-16-2012
    Location
    Space
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Dynamically add Border to desired range

    Thank you for the code. I'm not sure how it works, so I will have to do some research.

    Once I understand it, I will post an update.

    Thanks again.


    Edit:
    The code works great.
    Last edited by AR-51; 03-15-2013 at 12:52 AM.

  4. #4
    Registered User
    Join Date
    04-16-2012
    Location
    Space
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Dynamically add Border to desired range

    Can the code be modified to always select column 1 to 5?
    The border only goes as far as the first blank column.

    Thanks

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Dynamically add Border to desired range

    You can change the intersect area.

    Please Login or Register  to view this content.
    otherwise can you post example workbook with dummy data

  6. #6
    Registered User
    Join Date
    04-16-2012
    Location
    Space
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Dynamically add Border to desired range

    It looks like "Intersect" does not have an effect on the code. I changed the Intersect area, but it had no effect.
    I then removed it, and the code ran.

    Thanks again for your help

    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    04-16-2012
    Location
    Space
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Dynamically add Border to desired range

    This code is one step closer. The only thing missing is to stop it from putting a border around the header row.
    I got lost of info from http://support.microsoft.com/kb/291308

    Please Login or Register  to view this content.
    Last edited by AR-51; 03-17-2013 at 12:33 AM.

  8. #8
    Forum Contributor
    Join Date
    01-13-2013
    Location
    Oklahoma
    MS-Off Ver
    Office 2007 / Office 2010
    Posts
    123

    Re: Dynamically add Border to desired range

    Here's a function I use to border a range.
    Please Login or Register  to view this content.

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Dynamically add Border to desired range

    try
    Please Login or Register  to view this content.

  10. #10
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Dynamically add Border to desired range

    The empty spaces in your data means the currentregion was not picking up data on the far right.
    Try this modification.

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    04-16-2012
    Location
    Space
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Dynamically add Border to desired range

    Thank you Andy Pope, jindon and Yraen for your time.
    I added Resize to jindon code to get it to work. Andy Pope your code works perfectly.

    Edit:
    Wow, I did not notice, but Andy Pope your code exclude the title row. Thanks allot.

    Please Login or Register  to view this content.
    Last edited by AR-51; 03-17-2013 at 02:52 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1