Non Contiguous range and loops

[David]

The following code returns 1 ,2 , 3, and 4 for the row
numbers of the non contiguous range rng.
Sub Code1 ()
Dim rng As Range
Set rng = Range("A1,A5,A7,A12")
For c = 1 To 4'
myArray(c) = rng.Cells(c).Row
Next
End Sub

 

[Bob Phillips]

David,

You don't say, but I guess you want the absolute row numbers, rather than
the relative row to the range?

If so, try this

Sub Code1()
Dim rng As Range, c, myArray(4)
Set rng = Range("A1,A5,A7,A12")
For c = 1 To 4 '
myArray(c) = rng.Areas.Item(c).Row
Next
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

c = 1
for each cell in rng
myArray(c) = cell.row
c = c + 1
Next

 

[Chip Pearson]

David,

Try something like the following:

Dim Rng As Range
Dim Ar As Range
Dim C As Range
Set Rng = Range("A1,A5,A7,A12")

For Each Ar In Rng.Areas
For Each C In Ar
Debug.Print C.Row
Next C
Next Ar


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)