Get the total items in a Range 1

[barnard89]

Hi

I have a range in an excel sheet.
The row is always of a single column, but multiple rows

How can I get all the elements of the Range into a Collection
The Range can be continous like
Range("A1:A15").Select

or it can be discontinous like

Range("A1,A5,A10,A14,A17").Select

How can I get all the elements of that Range into a collection . I prefer to get the row numbers of the range into the collection

Please suggest

Thanks

 

[PHV]

You Range is already sort of a collection !
Proof of concept:
For Each c in Range("A1,A5,A10,A14,A17")
Debug.Print c.Row
Next

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[barnard89]

Hi PHV

Thanks for the reply

Now my Range in real world runs into some 8000 - 9000 rows
So I am not in a position to write a code like
For Each c in Range("A1,A5,A10,A14,A17")
Debug.Print c.Row
Next

Could you kindly suggest a modified method

Thanks in advance

 

[PHV]

I just explained you that if you have a Range object in your code then you don't need a Collection at all.
Sorry, I don't understand your issue.
Perhaps posting the code you have tried so far may help to see where is your problem.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sahmiele]

what are you trying to do with the range? Once I know, I can set it up for you. Also, are there distinguishing factors for the cells that go into the range, ie, there is data in them?

 

[barnard89]

Hi

With reference to the same problem this is the code what I have written

I am randomly selecting the items( in the same column) and
naming them as a range ( Range_1)

The items are the ones that I had selected ( ie
Selection.address )

I need to place the row numbers of all the cells that I had selected into a collection C1
Could you tell me what is wrong with the code


Public Sub M()
Dim i As Integer

MsgBox Selection.Address
Dim C1 As New Collection
Dim R1 As Range
ActiveWorkbook.Names.Add Name:="Range_1", RefersTo:="=Sheet1!Selection.Address"

For Each c In Range_1
C1.Add c.Row
Next
For i = 1 To C1.Count
MsgBox C1(i)
Next
End Sub

 

[firefytr]

As already requested, explain - in clear text - the PURPOSE of your code, not the code itself. We need to know this so we can suggest some *good* structured code that is efficient and elegant.

-----------
Regards,
Zack Barresse