What does the Excel range.Rows property really do?

Question

OK, I am finishing up an add-on project for a legacy Excel-VBA application, and I have once again run up against the conundrum of the mysterious range.Rows (?) and worksheet.Rows properties.

Does anyone know what these properties really do and what they are supposed to provide to me? (Note: all of this probably applies to the corresponding *.Columns properties also).

What I would really like to be able to use it for is to return a range of rows, like this:

   SET rng = wks.Rows(iStartRow, iEndRow) 

But I have never been able to get it to do that, even though the Intellisense shows two arguments for it. Instead I have to use one of the two or three other (very kludgy) techniques.

The help is very unhelpful (typically so for Office VBA), and googling for "Rows" is not very useful, no matter how many other terms I add to it.

The only things that I have been able to use it for are 1) return a single row as a range ( rng.Rows(i) ) and 2) return a count of the rows in a range ( rng.Rows.Count ). Is that it? Is there really nothing else that it's good for?

Clarification: I know that it returns a range and that there are other ways to get a range of rows. What I am asking for is specifically what do we get from .Rows() that we do not already get from .Cells() and .Range()? The two things that I know are 1) an easier way to return a range of a single row and 2) a way to count the number of rows in a range.

Is there anything else?

Answer 1

Range.Rows and Range.Columns return essentially the same Range except for the fact that the new Range has a flag which indicates that it represents Rows or Columns. This is necessary for some Excel properties such as Range.Count and Range.Hidden and for some methods such as Range.AutoFit():

• Range.Rows.Count returns the number of rows in Range.
• Range.Columns.Count returns the number of columns in Range.
• Range.Rows.AutoFit() autofits the rows in Range.
• Range.Columns.AutoFit() autofits the columns in Range.

You might find that Range.EntireRow and Range.EntireColumn are useful, although they still are not exactly what you are looking for. They return all possible columns for EntireRow and all possible rows for EntireColumn for the represented range.

I know this because SpreadsheetGear for .NET comes with .NET APIs which are very similar to Excel's APIs. The SpreadsheetGear API comes with several strongly typed overloads to the IRange indexer including the one you probably wish Excel had:

• IRange this[int row1, int column1, int row2, int column2];

Disclaimer: I own SpreadsheetGear LLC

Answer 2

Range.Rows, Range.Columns and Range.Cells are Excel.Range objects, according to the VBA Type() functions:

 ?TypeName(Selection.rows) Range 

However, that's not the whole story: those returned objects are extended types that inherit every property and method from Excel::Range - but .Columns and .Rows have a special For... Each iterator, and a special .Count property that aren't quite the same as the parent Range object's iterator and count.

So .Cells is iterated and counted as a collection of single-cell ranges, just like the default iterator of the parent range.

But .Columns is iterated and counted as a collection of vertical subranges, each of them a single column wide;

...And .Rows is iterated and counted as a collection of horizontal subranges, each of them a single row high.

The easiest way to understand this is to step through this code and watch what's selected:

 Public Sub Test() 
 Dim SubRange As Range Dim ParentRange As Range 
 Set ParentRange = ActiveSheet.Range("B2:E5") 

 For Each SubRange In ParentRange.Cells     SubRange.Select Next  
 For Each SubRange In ParentRange.Rows     SubRange.Select Next 
 For Each SubRange In ParentRange.Columns     SubRange.Select Next 
 For Each SubRange In ParentRange     SubRange.Select Next 
 End Sub 

Enjoy. And try it with a couple of merged cells in there, just to see how odd merged ranges can be.