how to select cells in worksheet based on their addresses

Question

if i have an 1 dimensional array and each element of the array carry cell address.

Now I want to select those cells in a worksheet of carrying these addresses
Hint the selection should be multi selection of all addresses Something like .select property
In order to do use the selection for further operations like selection.copy and so on

[Avoid Select and Activate](http://stackoverflow.com/q/10714251/1188513) — Mathieu Guindon, Apr 20 '17 at 20:50

score 3 · Answer 1 · answered Apr 20 '17 at 20:03

3

If your array is in a variable like "arr", then you could use something like this;

dim rng as Range

set rng=Sheet1.Range(Join(arr,","))

Now rng has will have your address in your array. But bear in mind, some operations such as copy may not be possible on such multiple selections. But could certainly do some formatting

answered Apr 20 '17 at 20:03

gr8tech

174
1
7

1

Can you provide more information. Did you get some sort of error and what did you intend to do with the range. – gr8tech Apr 21 '17 at 16:24

score 2 · Accepted Answer · answered Apr 20 '17 at 19:57

2

You would loop through the array and use Union:

Sub foo()
Dim rng As Range
Dim arr()
Dim i As Long

arr = Array("$A$1", "$CD$5", "$F$4", "$H$8")

For i = LBound(arr) To UBound(arr)
    If i = LBound(arr) Then
        Set rng = Worksheets("Sheet1").Range(arr(i))
    Else
        Set rng = Union(Worksheets("Sheet1").Range(arr(i)), rng)
    End If
Next i

Debug.Print rng.Address(0, 0, xlA1, 1)
End Sub

answered Apr 20 '17 at 19:57

Scott Craner

148,073
10
49
81

If I add this line to the code rng.select will it select those cells in the sheet? – walid attia Apr 20 '17 at 20:08
1

Yes, but it is not recommended that one uses select, it slows down the code. Just do what you want with the range directly. – Scott Craner Apr 20 '17 at 20:10

how to select cells in worksheet based on their addresses

2 Answers2