Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    interate through non-contiguous cells (Excel xp)

    I'm trying to make a loop from Columns D through AB

    ActiveSheet.Range("D89, D1213, D1617, D2021, D2425, D27").Select

    ActiveSheet.Range("E8:E9, E12:E13,E16:E17, E20:E21, E24:E25, E27").Select

    i don't know how to change the selection in a loop starting at D and moving one by one through the columns all the way to AB.

    How can i do this in a For/Next loop. Thank you very much

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: interate through non-contiguous cells (Excel xp)

    There's plenty of column loop code in the Forum, there's even something I posted recently. You can be a little lazy with Columns, such as the general form:

    For intCounter = 4 to 28 'columns D to AB
    With Columns(intCounter)
    .<do stuff>
    End With
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: interate through non-contiguous cells (Excel xp)

    ok, thanks.

    can i substitute it into this?

    ActiveSheet.Range("D89, D1213, D1617, D2021, D2425, D27").Select

    For iColumn = 4 To 32 'columns D to AG
    With Columns(iColumn)
    ActiveSheet.Range(iColumn & "8:" & iColumn & "9", D1213, D1617, D2021, D2425, D27").Select
    Call Arrows
    End With
    Next iColumn

    or is there an easier way?

  4. #4
    New Lounger
    Join Date
    Apr 2004
    Location
    Lancaster, Pennsylvania
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: interate through non-contiguous cells (Excel xp)

    Hi Jha900,

    Here is, what I believe to be, the best practice for your situation:

    Sub selectnoncontiguous()
    Dim multirange As Range
    Dim r1 As Range
    Dim r2 As Range
    Dim r3 As Range
    Dim r4 As Range
    Dim r5 As Range
    Dim r6 As Range


    For col = 4 To 28
    Set r1 = Range(Cells(8, col), Cells(9, col))
    Set r2 = Range(Cells(12, col), Cells(13, col))
    Set r3 = Range(Cells(16, col), Cells(17, col))
    Set r4 = Range(Cells(20, col), Cells(21, col))
    Set r5 = Range(Cells(24, col), Cells(25, col))
    Set r6 = Cells(27, col)
    Set multirange = Union(r1, r2, r3, r4, r5, r6)

    multirange.Select




    Next col

    End Sub


    Regards,

    Andy S.

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: interate through non-contiguous cells (Excel xp)

    Point of embarrassment: I thought I was in the Excel Forum when I responded, so my comment about posts in this Forum demonstrating looping through columns was wrong! <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    You can probably make this more efficient with something like (untested)

    For iColumn = 4 To 32 'columns D to AG
    Activesheet.Range(iColumn & "8:" & iColumn & "9", iColumn & "12:" & iColumn & "13", _
    iColumn & "16:" & iColumn & "17", iColumn & "20:" & iColumn & "21", iColumn & "24:" & iColumn & "25", _
    iColumn & "27").Select
    Call Arrows

    But it would be even better if you set this as a range and passed it to the called function, such as

    Dim rngTarget as Range
    For iColumn = 4 To 32 'columns D to AG
    Set rngTarget = Range(iColumn & "8:" & iColumn & "9", iColumn & "12:" & iColumn & "13, _
    iColumn & "16:" & iColumn & "17, iColumn & "20:" & iColumn & "21, iColumn & "24:" & iColumn & "25, _
    iColumn & "27")
    Call Arrows(rngTarget)

    The function would need to be modified to operate on the passed range instead of the .Selection.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: interate through non-contiguous cells (Excel xp)

    Andy Smith's Union range approach is very good. Regarding:

    > or is there an easier way?

    what is specific to the noncontiguous cells that you have to select them like that? An easier way might be to select the whole range and test each cell for a common condition, or to use SpecialCells Method.
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    New Lounger
    Join Date
    Apr 2004
    Location
    Lancaster, Pennsylvania
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: interate through non-contiguous cells (Excel xp)

    I like John's last approach that passes a range variable to a separate function. This is probably the most controlled method of selection.

    Actually, it is more efficient to select the noncontiguous cells in this manner than the union. I had attempted this approach, but ended up sidetracked by misplaced quotation marks. In order to work around this issue, I decided to use a union of separate ranges rather than ": ___ , ___:...etc"

    The choice is up to the programmer. Either approach works.

Posting Permissions

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