Non Contiguous range and loops
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 |
Non Contiguous range and loops
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) "David" wrote in message ... 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 |
Non Contiguous range and loops
c = 1
for each cell in rng myArray(c) = cell.row c = c + 1 Next ----------------- rng.cells(c) is an offset from the top left cell in the range. -- Regards, Tom Ogilvy "David" wrote in message ... 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 |
Non Contiguous range and loops
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 "David" wrote in message ... 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 |
All times are GMT +1. The time now is 03:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com