How to select a group of non-adjacent cells from cell addresses stored in an array?

Question

I have an 1 dimensional array of cell addresses (cells may be non-adjacent). I want to select all of these cells in order to change the interior color. The cells in the array represent all the cells in the workbook that have changed since the last version of the worksheet, and I need to highlight them in order to view the changes.

I tried converting the array to a string and then selecting them with the following code:

For index = 1 To UBound(RTemp)
    RTempStr = RTempStr & CStr(RTemp(index))
Next

RTempStr = Left(RTempStr, Len(RTempStr) - 1)

Worksheets("R1").Select
Union(RTempStr).Select
Selection.Interior = vbRed

RTemp is my array of cell addresses

The Union function returns a Type mismatch error

Any suggestions how to fix this? or alternate ways to select the cells?

Siddharth Rout · Answer 1 · 2013-10-17T13:01:24.287

Declare a range object and assign the non contiguous range to it. You can then directly change the interior color. See this example.

Sub sample()
    Dim rng As Range

    Set rng = ThisWorkbook.Sheets("Sheet1").Range("A1:A2,C4:C7,D11:D12")

    rng.Interior.ColorIndex = 3
End Sub

I am hard coding "A1:A2,C4:C7,D11:D12" If you have the cell addresses in an array then you can construct that string and pass it to the range object. For example

Sub sample()
    Dim Ar(1 To 3) As String
    Dim rng As Range
    Dim strAddress As String
    Dim i As Long

    Ar(1) = "A1:A2"
    Ar(2) = "C4:C7"
    Ar(3) = "D11:D12"

    For i = 1 To 3
        If strAddress = "" Then
            strAddress = Ar(i)
        Else
            strAddress = strAddress & "," & Ar(i)
        End If
    Next

    Set rng = ThisWorkbook.Sheets("Sheet1").Range(strAddress)

    rng.Interior.ColorIndex = 3
End Sub

Followup from comments

If the array has say Cell(4,5) then use the function that I created below.

Option Explicit

Sub Sample()
    Dim Ar(1 To 5) As String
    Dim rng As Range
    Dim strAddress As String
    Dim i As Long

    Ar(1) = "Cells(4,5)"
    Ar(2) = "Cells(6,6)"
    Ar(3) = "Cells(7,7)"
    Ar(4) = "Cells(8,12)"
    Ar(5) = "Cells(9,12)"

    strAddress = Join(Ar, "#") '<~~ `Join` Courtsey Gary's Student
    strAddress = GetAddress(strAddress)

    Debug.Print strAddress

    Set rng = ThisWorkbook.Sheets("Sheet1").Range(strAddress)

    rng.Interior.ColorIndex = 3
End Sub

Function GetAddress(sAddr As String) As String
    Dim MyAr
    Dim sTemp As String, sAddrs As Range
    Dim Rw As Long, Col As Long, i As Long

    MyAr = Split(sAddr, "#")

    For i = 0 To UBound(MyAr)
        sTemp = Replace(Replace(MyAr(i), "Cells(", ""), ")", "")
        Rw = Val(Trim(Split(sTemp, ",")(0)))
        Col = Val(Trim(Split(sTemp, ",")(1)))


        If sAddrs Is Nothing Then
            Set sAddrs = ThisWorkbook.Sheets(1).Cells(Rw, Col)
        Else
            Set sAddrs = Union(sAddrs, ThisWorkbook.Sheets(1).Cells(Rw, Col))
        End If
    Next i

    GetAddress = sAddrs.Address
End Function

Your method works perfectly, EXCEPT all my cell addresses are stored as cells(4,6),(8,9),... etc which doesn't seem to work with the range function. Is there an easy way to convert from the cells format to the range format? — PS apteryx, Oct 17 '13 at 12:07
See the Followup that I updated in the above post. You might have to refresh that. — Siddharth Rout, Oct 17 '13 at 13:01
I can change how I write the array, that's not a problem. But I writing an array of addresses referencing the columns by letter seems a really long way of doing it since everywhere else the cells columns are referenced by number. — PS apteryx, Oct 17 '13 at 13:06
Your updated code works beautifully, however there seems to be a limitation to the number of arguments you can use with the Union method. The end result was that the code worked for the first 24 ranges in the union method, and after that the code kept running but had no effect. In any case I have solved my initial problem using some of your ideas (I used the address function to edit the properties of each cell individually after it is added to the array) so thanks again. — PS apteryx, Oct 17 '13 at 15:35

score 1 · Answer 2 · answered Oct 17 '13 at 11:40

1

Just create the Address by Joining:

Sub jumble()
    Dim ary(1 To 3) As String
    Dim s As String
    Dim r As Range
    ary(1) = "A1"
    ary(2) = "B9"
    ary(3) = "C11"
    s = Join(ary, ",")
    Set r = Range(s)
    r.Select
End Sub

answered Oct 17 '13 at 11:40

Gary's Student

95,722
10
59
99

[INTERESTING READ](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) – Siddharth Rout Oct 17 '13 at 11:43

How to select a group of non-adjacent cells from cell addresses stored in an array?

2 Answers2