vba Excel range.Rows 属性到底有什么作用？

What does the Excel range.Rows property really do?

提问by RBarryYoung

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.Rowsproperties.

好的，我正在完成一个遗留 Excel-VBA 应用程序的附加项目，我再次遇到了神秘range.Rows(?) 和worksheet.Rows属性的难题。

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 *.Columnsproperties also).

有谁知道这些属性的真正作用以及它们应该为我提供什么？（注意：所有这些可能也适用于相应的*.Columns属性）。

What I would really liketo 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.

该帮助非常无用（对于 Office VBA 而言通常如此），并且无论我添加了多少其他术语，谷歌搜索“行”都不是很有用。

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?

我能够使用它的唯一事情是 1) 返回单行作为范围 ( rng.Rows(i)) 和 2) 返回范围 ( ) 中的行数rng.Rows.Count。是这样吗？真的没有其他好处了吗？

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.

澄清：我知道它返回一个范围，并且还有其他方法可以获取一系列行。我所要求的具体是我们从哪些方面获得.Rows()了我们尚未获得的.Cells()和.Range()？我知道的两件事是 1) 一种返回单行范围的更简单方法和 2) 一种计算范围内行数的方法。

Is there anything else?

还有别的事吗？

采纳答案by Joe Erickson

Range.Rowsand Range.Columnsreturn 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并Range.Columns返回基本相同的范围，除了新范围有一个标志表示它代表行或列。这对于某些 Excel 属性（例如 Range.Count 和 Range.Hidden）以及某些方法（例如Range.AutoFit()：

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

• Range.Rows.Count返回 Range 中的行数。
• Range.Columns.Count返回范围中的列数。
• Range.Rows.AutoFit()自动调整范围中的行。
• Range.Columns.AutoFit()自动调整范围中的列。

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

您可能会发现它Range.EntireRow并且Range.EntireColumn很有用，尽管它们仍然不是您正在寻找的。它们返回表示范围的所有可能的列EntireRow和所有可能的行EntireColumn。

I know this because SpreadsheetGear for .NETcomes 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:

我知道这一点是因为用于 .NET 的 SpreadsheetGear带有与 Excel 的 API 非常相似的 .NET API。SpreadsheetGear API 为 IRange 索引器提供了几个强类型重载，包括您可能希望 Excel 拥有的重载：

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

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

Disclaimer: I own SpreadsheetGear LLC

免责声明：我拥有 SpreadsheetGear LLC

回答by Nigel Heffernan

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

根据 VBA Type() 函数，Range.Rows、Range.Columns 和 Range.Cells 是 Excel.Range 对象：

?TypeName(Selection.rows)
Range

然而，这还不是全部：那些返回的对象是继承 Excel::Range 的每个属性和方法的扩展类型 - 但是 .Columns 和 .Rows 有一个特殊的 For... 每个迭代器，以及一个特殊的 .Count 属性't quite相当同父Range对象的迭代器和计数。

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

所以 .Cells 被迭代并计数为单个单元格范围的集合，就像父范围的默认迭代器一样。

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

但是 .Columns 被迭代并算作垂直子范围的集合，每个子范围只有一列宽；

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

...并且 .Rows 被迭代并计算为水平子范围的集合，每个子范围都是单行高。

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

享受。并在那里尝试几个合并的单元格，看看如何odd奇数合并范围可以。

回答by e.James

Your two examples are the only things I have ever used the Rowsand Columnsproperties for, but in theory you could do anything with them that can be done with a Rangeobject.

你的两个例子是我唯一使用过Rows和Columns属性的东西，但理论上你可以用它们做任何可以用Range对象完成的事情。

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

这些属性的返回类型本身就是 a Range，因此您可以执行以下操作：

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).

这将选择myRange（Sheet1 中的单元格 B4:H4）中的第三行。

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:

更新 #2：或者，从另一个范围内获取行的子集：

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))

回答by mrento

Since the .Rows result is marked as consisting of rows, you can "For Each" it to deal with each row individually, like this:

由于 .Rows 结果被标记为由行组成，您可以“For Each”它来单独处理每一行，如下所示：

Function Attendance(rng As Range) As Long
Attendance = 0
For Each rRow In rng.Rows
    If WorksheetFunction.Sum(rRow) > 0 Then
        Attendance = Attendance + 1
    End If
Next
End Function

I use this to check attendance in any of a few categories (different columns) for a list of people (different rows).

我使用它来检查人员列表（不同行）的几个类别（不同列）中的任何一个的出勤情况。

(And of course you could use .Columns to do a "For Each" over the columns in the range.)

（当然，您可以使用 .Columns 对范围内的列执行“For Each”。）

回答by Nick Mellor

I've found myself using range.Rows for its effects in the Copy method. It copies the height of the rows from the origin to the destination, which is the behaviour I want.

我发现自己在 Copy 方法中使用 range.Rows 来实现其效果。它将行的高度从起点复制到终点，这是我想要的行为。

rngLastRecord.Rows.Copy Destination:=Sheets("Availability").Range("a" & insertRow)

If I had used rngLastRecord.Copy instead of rngLastRecord.Rows.Copy, the row heights would be whatever was there before the copy.

如果我使用 rngLastRecord.Copy 而不是 rngLastRecord.Rows.Copy，行高将是复制之前的任何值。

回答by Joel Goodwin

I'm not sure, but I think the second parameter is a red herring.

我不确定，但我认为第二个参数是一个红鲱鱼。

Both .Rows and .Columns take two optional parameters: RowIndex and ColumnIndex. Try to use ColumnIndex, e.g. Rows(ColumnIndex:=2), generates an error for both .Rows and .Columns.

.Rows 和 .Columns 都有两个可选参数：RowIndex 和 ColumnIndex。尝试使用 ColumnIndex，例如Rows(ColumnIndex:=2)，为.Rows 和 .Columns生成错误。

My feeling it's inherited in some sense from the Cells(RowIndex,ColumnIndex)Property but only the first parameter is appropriate.

我觉得它在某种意义上是从Cells(RowIndex,ColumnIndex)属性继承的，但只有第一个参数是合适的。

回答by jswolf19

It's perhaps a bit of a kludge, but the following code does what you seem to want to do:

这可能有点麻烦，但以下代码执行您似乎想要做的事情：

Set rng = wks.Range(wks.Rows(iStartRow), wks.Rows(iEndRow)).Rows

回答by Paolo Bortolini

I've found this works:

我发现这有效：

Rows(CStr(iVar1) & ":" & CStr(iVar2)).Select

回答by blash

There is another way, take this as example

还有一种方法，以这个为例

Dim sr As String    
sr = "6:10"
Rows(sr).Select

All you need to do is to convert your variables iStartRow, iEndRowto a string.

您需要做的就是将您的变量iStartRow,iEndRow转换为字符串。