Named rang referto size limit

[HighAndWilder]

Hi All

I'm trying to create a number of named ranges made up of a non-contigious cell references.

These are references to cells containing the time taken to compete a running race. I will then use the RANK funtion to rank the runners.

I understand that there is a limit to the length of the Refersto string, 250 characters I think.

How does one get around this problem in VBA?

I can find another way of Ranking the runners but the RANK function is really the right way to go.

I'll appreciate any help that you can give me.

Take care and stay safe.

 

[pgc01]

Hi

In vba you can use Union to build a non-contiguous range.

For ex., this code builds a non-contiguous range with 50 cells in column A and then uses Rank().

Sub Test()
Dim r As Range, rc As Range
Dim j As Long

Set r = Range("A1")
r.Value = 10

For j = 3 To 100 Step 2
    Set rc = Range("A" & j)
    rc.Value = j * 10
    Set r = Application.Union(r, rc)
Next j

MsgBox Application.WorksheetFunction.Rank(70, r)

End Sub

 

[HighAndWilder]

Thanks for you reply PGC01.

There is a limit as to how many non-contigious ranges can be included irrespective of whether they be individual cells ranges or multiple cell ranges and this is based on
the length of the address of range r. The length cannot exceed 255 characters. No more ranges are added if the addition of another one will take the length of the address of r above 255.

It is interesting that an error is not raised becuse the loop continues and further attempts to add ranges are made.

I will need to find another way of doing this.

 

[pgc01]

There is a limit as to how many non-contigious ranges can be included irrespective of whether they be individual cells ranges or multiple cell ranges and this is based on
the length of the address of range r. The length cannot exceed 255 characters.

??

Not true. In vba you can use union with any number of cells (unless you want to select more than 2 billion cells)

Maybe you are referring to a limit of the address string, but you don't need it to work with a multi-area range.


To see that Union is adding all the cells to the range, try this quick test: Replace in the code I posted

MsgBox Application.WorksheetFunction.Rank(70, r)

with

MsgBox "Count: " & r.Count & ", sum:" & Application.WorksheetFunction.Sum(r)

You'll see that vba counts all the 50 cells and also adds them all, equal 25000, as expected.

 

[HighAndWilder]

Thanks PGC

It did seem a bit weird.

Back to the original idea now but putting the range together using Union.

Thanks again.

 

[pgc01]

I'm glad it helped.