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:




6 comments:

  1. Your picture is the result of

    Range("A1, B5, C9")

    not

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

    ReplyDelete
  2. 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. 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

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group