What does the Excel range.Rows property really do?

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

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

?TypeName(Selection.rows)
Range

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

Your two examples are the only things I have ever used the Rows and Columns properties for, but in theory you could do anything with them that can be done with a Range object.

The return type of those properties is itself a Range, so you can do things like:

Dim myRange as Range
Set myRange = Sheet1.Range(Cells(2,2),Cells(8,8))
myRange.Rows(3).Select

Which will select the third row in myRange (Cells B4:H4 in Sheet1).

update: To do what you want to do, you could use:

Dim interestingRows as Range
Set interestingRows = Sheet1.Range(startRow & ":" & endRow)

update #2: Or, to get a subset of rows from within a another range:

Dim someRange As Range
Dim interestingRows As Range

Set myRange = Sheet1.Range(Cells(2, 2), Cells(8, 8))

startRow = 3
endRow = 6

Set interestingRows = Range(myRange.Rows(startRow), myRange.Rows(endRow))