 # Row And Column Ranges Are Unique?

• What's unique about row and column ranges compared to vanilla ranges?

1. This works (it calculates the deviation of each column in a range):

This doesn't:

2. In the second example, rCol.Count (the general property that returns the count of members in a collection, of which a range are one) returns 1; rCol.Cells.Count of course returns 5.

3. You can't index a row or column range; e.g., rCol(1) errors -- you have to do rCol.Cells(1).

Whassup with that?

[SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

• Re: Row And Column Ranges Are Unique?

shg
I am not sure but I think is because the columns and rows only exist if there is a cell.
so the cell is the base object that you are assigning the range to
But I have been wrong many times before

Jim
"The problem with designing vba code completely foolproof is to underestimate the ingenuity of a complete fool."

• Re: Row And Column Ranges Are Unique?

Try;

Code
1. r.Columns.Cells

Or simply r if the range is a single column.

• Re: Row And Column Ranges Are Unique?

Quote

Or simply r if the range is a single column.

That's my point; a normal single-column range is somhow different than a member of the columns collection of a normal rectangular range. Look at the code examples; why shouldn't the second one work?

[SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

• Re: Row And Column Ranges Are Unique?

Code
1. For Each rCol In r.Columns

I think you need to specify which column. That is;

Code
1. For Each rCol In r.Columns(1)

and perhaps even;

Code
1. For Each rCol In r.Columns(1).Cells

as Columns is representing a Property of the Range, not a Range itself.

• Re: Row And Column Ranges Are Unique?

The variable rCol is declared as a Range, and its TypeName is "Range". It's just different than a vanilla range for no reason I can see. When is a range not a range?

[SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

• Re: Row And Column Ranges Are Unique?

Yes, rCol is a range, as is r. BUT r.Columns results in Columns being a Property of r, the Range Object.

• Re: Row And Column Ranges Are Unique?

SHG,

Quote

Look at the code examples; why shouldn't the second one work?

Will not work because rCol does not represent a cell it represents a column of cells. And there for you cant get the value of a column of cells. Hence the error on this line:

Code
1. Func(iCol) = WF.StDev(rCol.Value)

Try this to see what I mean:

And further:

There is nothing wrong with this syntax:

Code
1. For Each rCol In r.Columns
• Re: Row And Column Ranges Are Unique?

You guys are are skirting the question. If each range in r.columns is not a vanilla range, exactly what manner of beast is it, and why can't it be indexed and counted as a range?

[SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

• Re: Row And Column Ranges Are Unique?

I have told you twice what r.columns is. I really don't know how else I can spell it out, sorry.

• Re: Row And Column Ranges Are Unique?

Quote from shg

You guys are are skirting the question.

No not at all im just not sure what you are asking.

Quote from shg

If each range in r.columns is not a vanilla range,

Whats a vanilla range?

Quote from shg

exactly what manner of beast is it, and why can't it be indexed and counted as a range?

Exactly, why are you saying it cant.

• Re: Row And Column Ranges Are Unique?

Quote from shg

You guys are are skirting the question.

That was a stupid thing to say, I apologize.

Quote from Reafidy

Will not work because rCol does not represent a cell it represents a column of cells. And there for you can't get the value of a column of cells.

Sure you can -- how about:

Code
1. someVariant = someRange.Value 'or
2. someDouble = WorksheetFunction.Sum(someRange.Value) ' or just
3. someDouble = WorksheetFunction.Sum(someRange)

Change StDev to Sum. You can always sum a range, right? Again, it works in the first example but not in the second. So what makes a range selected from the Columns collection of a range any different that a normal single-column range?

Quote from Reafidy

Whats a vanilla range?

Sorry, I guess that's an Americanism -- a plain old garden-variety everyday range.

Quote from Dave

Yes, rCol is a range, as is r. BUT r.Columns results in Columns being a Property of r, the Range Object.

Columns is a property that returns the Columns collection, whose members are ranges. And the For Each loops returns members of the collection, which are ranges, to rCol, which is a range. What else could a range variable hold but a range? And if it's a range, why does it behave differently (its Count property, the fact that it cannot be summed) than any other range?

[SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

• Re: Row And Column Ranges Are Unique?

shg, to my relatively untutored eye your logic seems, and I think is, impeccable. I think the answer lies in these lines

Code
1. Dim iCol As Long
2. Dim Func(1 To nCol) As Double
3. Func(iCol) = WF.StDev(rCol.Value)

So iCol starts at zero. If you initialise iCol as 1 then your second bit of code works.

• Re: Row And Column Ranges Are Unique?

OMG. :redface: :redface: :redface: :crying:

Thank you, StephenR, the loop should (of course) increment iCol:

Code
1. For Each rCol In r.Columns
2. iCol = iCol + 1
3. Func(iCol) = WF.Sum(rCol.Value)
4. Next rCol

I was so focused in the strange indexing thing that I had a brain cramp in the subscript error.

Any thoughts on that?

[SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

• Re: Row And Column Ranges Are Unique?

Any thoughts on your brain cramp?!

I recently spent ten or fifteen minutes scratching my head over why a countif formula didn't work before realising I had the range and criteria the wrong way round.

Sorry, you mean the indexing...Well this works:

Code
1. For Each rCol In r.Columns
2. iCol = iCol + 1
3. Func(iCol) = WF.StDev(rCol(1).Value)
4. MsgBox Func(iCol)
5. Next rCol

Is that what you mean?

Btw I like the worksheetfunction bit, haven't seen that before.