variant array containing cel adresses > convert to actual ranges-array

  • Thread starter Thread starter Peter
  • Start date Start date
P

Peter

Hi everyone,

In the macro I've already produced so far, which works except for this here
little part, a variant array is filled with absolute cell adresses. When the
macro is finished doing the bulk of the work, a range needs to be selected
consisting of all the cells gathered in the CellAdresses array.

This doesn't work: Range(CelAdresses).Select, obviously (I tried it anyway,
just to see what I already knew), because the array is Dimmed as a variant.
Switching the Dim statement to make it into a range array also causes a
runtime error.

How can I use the CellAdresses array with its contents to get the selection
of separate cells I want?
Does the array need to be converted into a range array, if yes, How ?

Regards,
Peter
 
Dim rng as Range
set rng = Range(celadresses(lbound(celadresses)))
for i = lbound(celadresses)+1 to ubound(celadresses)
set rng = union(range(celadresses(i)),rng)
Next
rng.Select
 
Thanks Tom for your quick reply.

It made perfect sence. However after implementing it into my code it got
this error:

Run-time error '1004':



Method 'Range' of object '_Global' failed


On this line:

Set rng = Range(CelAdresses(LBound(CelAdresses)))

Maybe you could tell me some solutions for this problem.

Again, thanks for what you've already done.

Peter
 
Not sure how your data is set up, but would this work for you?

Range(Join(CellAdresses, ",")).Select
 
I combined this with some modified code I wrote for someone else to populate
the array celadresses. I tested it and it worked fine for me:

Sub Tester1()
Dim rng1 As Range, cell As Range
Dim celadresses() As String, i As Long
Dim rng As Range, ar As Range
For Each cell In Range("A1:C1,D1,B3:F3,J6,J1,F5:F6")
If rng1 Is Nothing Then
Set rng1 = cell
Else
Set rng1 = Union(rng1, cell)
End If
Next
Debug.Print rng1.Address
ReDim celadresses(1 To rng1.Areas.Count)
i = 0
For Each ar In rng1.Areas
i = i + 1
celadresses(i) = ar.Address
Debug.Print i, celadresses(i)
Next


Set rng = Range(celadresses(LBound(celadresses)))
For i = LBound(celadresses) + 1 To UBound(celadresses)
Set rng = Union(Range(celadresses(i)), rng)
Next
rng.Select

End Sub
 
I suspect that CelAdresses(LBound(CelAdresses)), i.e., the first
element of CelAdresses, does not contain a valid address. What is in
it when you get the error?

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top