Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Select multiple ranges in VBA (XL 2002 SP3)

    Hi all,

    in a macro I use, I would like to do some changes to the properties of cells. So far no problem, but I would like to do this on multiple ranges in one go. Say I want to change the same properties on Range("A1:B2") and Range("E1:E2"), I would use code like :

    <font color=blue>Range("A1:B2").Borders(xlEdgeTop).LineStyle = xlNone
    Range("E1:E2").Borders(xlEdgeTop).LineStyle = xlNone
    </font color=blue>

    Is there a way to optimize this sort of code ?
    <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    MTIA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select multiple ranges in VBA (XL 2002 SP3)

    You can use code like this:

    Union(Range("A1:B2"), Range("E1:E2")).Borders(xlEdgeTop).LineStyle = xlNone

    or

    Range("A1:B2,E1:E2").Borders(xlEdgeTop).LineStyle = xlNone

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select multiple ranges in VBA (XL 2002 SP3)

    Thank you (again), Hans.

    When using the second method, can one expand the selection even further ? I mean, can one use :

    <font color=blue>Range("A1:B2,E1:E2,F3:F5").Borders(xlEdgeTop).Line Style = xlNone</font color=blue>

    to include F3:F5 into the combined Range? If so, are there limits to how many ranges one can put in the expression :

    <font color=blue>Range (".....")</font color=blue> <img src=/S/question.gif border=0 alt=question width=15 height=15>

    Regards

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Select multiple ranges in VBA (XL 2002 SP3)

    Yes, you can expand this. When I tried, I could get up to 70 areas, after that I got an error.

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select multiple ranges in VBA (XL 2002 SP3)

    You might be better off using a named range instead of a hardcoded address. This will avoid trouble when you want to insert or delete columns or rows.
    Named ranges can hold up to 224 areas. Alas the only way to edit them is by using my Name Manager
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select multiple ranges in VBA (XL 2002 SP3)

    Hmmm, I did not think of using the "named range" in my problem. Thank you, Jan Karel, for sharing your point of view. I just downloaded and installed your Name Manager, and now I am reading the manual. Looks very good, indeed. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

    Regards

  7. #7
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Select multiple ranges in VBA (XL 2002 SP3)

    70 areas before getting an error message. That should be enough for my application (at this moment no more then 3, but likely to grow), so I will be able to optimize my code again a bit further. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

    Hans, you've been very helpful to me the last months, so I would like to thank you very, very much. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Of course I do not forget all the other Lounge members who help out people with their problems (so also a warm thank you to all of them), but you always seem to be on top of the newly entered posts.

    Although this is not the right forum to do so, I would like to grab this opportunity to wish all everybody a Merry Xmas and a Happy New Year ! <img src=/S/thewave.gif border=0 alt=thewave width=225 height=33>

    Best regards

Posting Permissions

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