Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Feb 2001
    Location
    North Carolina, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selecting non-contiguous cells from a macro

    I may be brain dead... it's been a long day....
    I need some help on selecting 6 non-contiguous cell on a worksheet from a macro.
    Have tried something like:
    Range("C16,C19,C22,C25,C28,C31").Select

    and the same type command using named ranges for each
    but it keeps bombing out on me.
    I know I could use
    TopCell.offset(3,0).select
    , etc. to get to each -- BUT I really want to be able to
    select them in one statement.
    Thanks for your help.
    Phyl

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting non-contiguous cells from a macro

    try this:

    application.union(Range("C16,C19,C22,C25,C28,C31") ).Select

  3. #3
    Lounger
    Join Date
    Feb 2001
    Location
    North Carolina, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting non-contiguous cells from a macro

    Thanks for the hint! I adjusted the syntax a little from:
    application.union(Range("C16,C19,C22")).Select

    and got the following to work (shortened for viewing purposes) and assigned it to a Variable:
    zAvgS1 = Application.Union(Range("C16"), Range("C19"),_
    Range("C22")).Select

    So now I can finally select the non-contiguous range! Great..but I now want to be able to move the selection over a column and tried using something like:

    Selection.Offset(0, 1).select
    or Range(zAvgS1).offset(0,1).Select

    But the offset selection ends up being only the ActiveCell
    (D16) rather than all three (D16, D19 and D22).
    What am I doing wrong? Does it have anything to do with Areas?
    Thanks, Phyl

  4. #4
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    120
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting non-contiguous cells from a macro

    This will select every third cell in the column of the active cell:

    Dim rng As Range
    Dim intColumn As Integer
    Dim intRow As Integer
    intColumn = ActiveCell.Column
    intRow = 16
    Set rng = Worksheets("sheet1").Cells(intRow, intColumn)
    Do Until intRow > 31
    intRow = intRow + 3
    Set rng = Application.Union(rng, ActiveSheet.Cells(intRow, intColumn))
    Loop
    rng.Select


    If you want to offset the column just add the offset to intColumn

  5. #5
    Lounger
    Join Date
    Feb 2001
    Location
    North Carolina, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting non-contiguous cells from a macro

    Thanks! It works like a charm. However, I am still curious about why I could not get the offset with the selected range set as a variable. Could it have something to do with areas -- any ideas why all I got was the ActiveCell?
    Thanks again for your assistance.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting non-contiguous cells from a macro

    The statement:

    <pre> Selection.Offset(0,1).Select
    </pre>


    Seems to work perfectly in Excel 2000. If I select cells C16, C19, and C22, then executing that statement moves the selection to D16, D19, and D22.

    However, I also tested it on Excel 97SR2, and it appears that there is a bug in that version. On Excel 97, the resulting selection is a single cell that is offset from whichever cell in the old selection was the first one selected. If I select cells C16, C19, and C22, then the statement will select cell D16. However, if I select cells C22, C19, then C16, then the resulting selection will be cell D22. Are you doing this on Excel 97 or earlier?

    The following somewhat strange looking code will accomplish what you are trying to do in Excel 97:

    <pre>Dim oCell As Range, oNew As Range
    Set oNew = ActiveCell.Offset(0, 1)
    For Each oCell In Selection
    Set oNew = Union(oNew, oCell.Offset(0, 1))
    Next oCell
    oNew.Select
    </pre>

    Legare Coleman

  7. #7
    Lounger
    Join Date
    Feb 2001
    Location
    North Carolina, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Selecting non-contiguous cells from a macro

    Lagare, Just got your input.
    Yes, I am using the deadly patched Excel97 SR2!
    I will go and play with your suggestion to see how it does.
    Back later -- and THANKS

Posting Permissions

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