Pages

▼

Sunday, February 22, 2009

How to use non-contiguous range of cells in Excel VBA

Here is a way to update a range that is not contiguous using VBA

Sub NonContiguous_Range_Example()

Dim oRng As Range

Set oRng = Range("A1, B5, C9")

oRng.Value = "45"

oRng.Interior.ColorIndex = 34

End Sub

The output will be as shown below:




at

6 comments:

  1. Anonymous6:17 AM

    Your picture is the result of

    Range("A1, B5, C9")

    not

    Range("A:A, B5, C9")

    ReplyDelete
  2. M Shasur7:42 AM

    Many Thanks John. I have corrected it

    ReplyDelete
  3. Anonymous2:02 PM

    What if you want to build a non-contiguous range without hard-coding the addresses?

    ReplyDelete
  4. Anonymous10:25 AM

    what is noncontiuos range of cells

    ReplyDelete
  5. Excel add-in user4:15 AM

    How do i select a range non-contiguous range?

    I have built a range using a union:

    this works ok:

    Set range_to_select = Union(cell, Range("a1"))
    range_to_select.Font.Bold = True

    but when I try to select the range, it goes crazy:


    Set range_to_select = Union(cell, Range("a1"))
    range_to_select.select

    am i missing a "cells" somewhere?

    ReplyDelete
  6. Anonymous4:12 AM

    Excel add-in user

    In both your examples shown "cell" does not represent a range in fact it does not represent anything.

    If you wanted a union on say cells(5,10) you could use:

    Set range_to_select = Union(cells(5,10), Range("a1"))
    range_to_select.Font.Bold = True

    and

    Set range_to_select = Union(cells(5,10), Range("a1"))
    range_to_select.select

    ReplyDelete