blocks|key|3318833|text|您的两个示例是我使用过的唯一Rows和Columns属性，但从理论上讲，您可以用它们做任何可以用Range对象做的事情。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3318834|这些属性的返回类型本身就是一个Range，因此您可以执行以下操作：|3318835|Dim+myRange+as+Range
Set+myRange+=+Sheet1.Range(Cells(2,2),Cells(8,8))
myRange.Rows(3).Select|code-block|syntax|javascript|3318836|它将选择myRange中的第三行(Sheet1中的单元格B4:H4+)。|3318837|更新：要做你想做的事情，你可以使用：|BOLD|3318838|Dim+interestingRows+as+Range
Set+interestingRows+=+Sheet1.Range(startRow+&+":"+&+endRow)|3318839|update+#2:或者，从另一个范围中获取行的子集：|3318840|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))|3318841|entityMap^0|E|4|J|7|1C|5|0|F|5|0|0|4|7|0|0|3|0|0|0|A|0|0^^$0|@$1|2|3|4|5|6|7|Z|8|@$9|10|A|11|B|C]|$9|12|A|13|B|C]|$9|14|A|15|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|16|8|@$9|17|A|18|B|C]]|D|@]|E|$]]|$1|H|3|I|5|J|7|19|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|1A|8|@$9|1B|A|1C|B|C]]|D|@]|E|$]]|$1|O|3|P|5|6|7|1D|8|@$9|1E|A|1F|B|Q]]|D|@]|E|$]]|$1|R|3|S|5|J|7|1G|8|@]|D|@]|E|$K|L]]|$1|T|3|U|5|6|7|1H|8|@$9|1I|A|1J|B|Q]]|D|@]|E|$]]|$1|V|3|W|5|J|7|1K|8|@]|D|@]|E|$K|L]]|$1|X|3|-4|5|6|7|1L|8|@]|D|@]|E|$]]]|Y|$]]

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

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

<pre><code>Dim myRange as Range
Set myRange = Sheet1.Range(Cells(2,2),Cells(8,8))
myRange.Rows(3).Select
</code></pre>

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

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

<pre><code>Dim interestingRows as Range
Set interestingRows = Sheet1.Range(startRow &amp; ":" &amp; endRow)
</code></pre>

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

<pre><code>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))
</code></pre>

blocks|key|338177|text|我不确定，但我认为第二个参数是一个转移注意力的问题。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|338178|.Rows和.Columns都有两个可选参数:+RowIndex和ColumnIndex。尝试使用ColumnIndex，例如Rows(ColumnIndex:=2)，会为+.Rows和.Columns生成错误。|offset|length|style|CODE|BOLD|338179|我觉得它在某种意义上继承自Cells(RowIndex,ColumnIndex)属性，但只有第一个参数是合适的。|338180|entityMap^0|0|1R|K|2E|F|0|D|R|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@$D|O|E|P|F|G]|$D|Q|E|R|F|H]]|9|@]|A|$]]|$1|I|3|J|5|6|7|S|8|@$D|T|E|U|F|G]]|9|@]|A|$]]|$1|K|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|L|$]]

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. <code>Rows(ColumnIndex:=2)</code>, generates an error for both .Rows and .Columns.

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

blocks|key|3318923|text|Range.Rows和Range.Columns返回的范围基本上相同，只是新的范围有一个标志，该标志表示行或列。这对于Range.Count和Range.Hidden等Excel属性以及Range.AutoFit()等方法是必需的|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3318924|3318925|Range.Rows.Count返回Range.|unordered-list-item|3318926|Range.Columns.Count中的行数返回Range.|3318927|Range.Rows.AutoFit()中的列数自适应Range.|3318928|Range.Columns.AutoFit()中的行自适应范围内的列。|3318929|3318930|您可能会发现Range.EntireRow和Range.EntireColumn很有用，尽管它们仍然不是您想要的。对于表示的范围，它们返回EntireRow的所有可能列和EntireColumn的所有可能行。|3318931|我之所以知道这一点，是因为SpreadsheetGear+for+.NET附带了与.NET+API非常相似的Excel。Excel附带了几个对IRange索引器的强类型重载，其中包括您可能希望SpreadsheetGear具有的一个：|3318932|3318933|IRange+this[int+row1,+int+column1,+int+row2,+int+column2];|3318934|3318935|免责声明:我拥有SpreadsheetGear有限公司|3318936|entityMap|0|LINK|mutability|MUTABLE|url|http://www.spreadsheetgear.com/^0|0|A|B|D|2L|F|0|0|0|G|0|0|J|0|0|K|0|0|N|0|0|6|F|M|I|1X|9|2D|C|0|D|O|0|0|0|0|1M|0|0|0^^$0|@$1|2|3|4|5|6|7|18|8|@$9|19|A|1A|B|C]|$9|1B|A|1C|B|C]|$9|1D|A|1E|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|1F|8|@]|D|@]|E|$]]|$1|G|3|H|5|I|7|1G|8|@$9|1H|A|1I|B|C]]|D|@]|E|$]]|$1|J|3|K|5|I|7|1J|8|@$9|1K|A|1L|B|C]]|D|@]|E|$]]|$1|L|3|M|5|I|7|1M|8|@$9|1N|A|1O|B|C]]|D|@]|E|$]]|$1|N|3|O|5|I|7|1P|8|@$9|1Q|A|1R|B|C]]|D|@]|E|$]]|$1|P|3|-4|5|6|7|1S|8|@]|D|@]|E|$]]|$1|Q|3|R|5|6|7|1T|8|@$9|1U|A|1V|B|C]|$9|1W|A|1X|B|C]|$9|1Y|A|1Z|B|C]|$9|20|A|21|B|C]]|D|@]|E|$]]|$1|S|3|T|5|6|7|22|8|@]|D|@$9|23|A|24|1|25]]|E|$]]|$1|U|3|-4|5|6|7|26|8|@]|D|@]|E|$]]|$1|V|3|W|5|I|7|27|8|@$9|28|A|29|B|C]]|D|@]|E|$]]|$1|X|3|-4|5|6|7|2A|8|@]|D|@]|E|$]]|$1|Y|3|Z|5|6|7|2B|8|@]|D|@]|E|$]]|$1|10|3|-4|5|6|7|2C|8|@]|D|@]|E|$]]]|11|$12|$5|13|14|15|E|$16|17]]]]

<code>Range.Rows</code> and <code>Range.Columns</code> 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 <code>Range.AutoFit()</code>:

<ul>
<li><code>Range.Rows.Count</code> returns the number of rows in Range.</li>
<li><code>Range.Columns.Count</code> returns the number of columns in Range.</li>
<li><code>Range.Rows.AutoFit()</code> autofits the rows in Range.</li>
<li><code>Range.Columns.AutoFit()</code> autofits the columns in Range.</li>
</ul>

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

I know this because <a href="http://www.spreadsheetgear.com/" rel="noreferrer">SpreadsheetGear for .NET</a> 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:

<ul>
<li><code>IRange this[int row1, int column1, int row2, int column2];</code></li>
</ul>

Disclaimer: I own SpreadsheetGear LLC

blocks|key|341652|text|这可能有点笨拙，但下面的代码做了您似乎想要做的事情：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|341653|Set+rng+=+wks.Range(wks.Rows(iStartRow),+wks.Rows(iEndRow)).Rows|code-block|syntax|javascript|341654|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>Set rng = wks.Range(wks.Rows(iStartRow), wks.Rows(iEndRow)).Rows
</code></pre>

blocks|key|336801|text|还有另一种方法，以此为例|type|unstyled|depth|inlineStyleRanges|entityRanges|data|336802|Dim+sr+As+String++++
sr+=+"6:10"
Rows(sr).Select|code-block|syntax|javascript|336803|你所需要做的就是将你的变量iStartRow，iEndRow转换成一个字符串。|offset|length|style|CODE|336804|entityMap^0|0|0|D|9|N|7|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]|$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

There is another way, take this as example

<pre><code>Dim sr As String 
sr = "6:10"
Rows(sr).Select
</code></pre>

All you need to do is to convert your variables <code>iStartRow</code>, <code>iEndRow</code> to a string.

blocks|key|3106344|text|我发现自己在Copy方法中使用range.Rows来实现它的效果。它将行的高度从源复制到目标，这就是我想要的行为。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3106345|rngLastRecord.Rows.Copy+Destination:=Sheets("Availability").Range("a"+&+insertRow)|code-block|syntax|javascript|3106346|如果我使用的是rngLastRecord.Copy而不是rngLastRecord.Rows.Copy，那么行高将是复制之前的值。|3106347|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

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.

<pre><code>rngLastRecord.Rows.Copy Destination:=Sheets("Availability").Range("a" &amp; insertRow)
</code></pre>

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

blocks|key|3319119|text|我发现这是可行的：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3319120|Rows(CStr(iVar1)+&+":"+&+CStr(iVar2)).Select|code-block|syntax|javascript|3319121|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

I've found this works:

<pre><code>Rows(CStr(iVar1) &amp; ":" &amp; CStr(iVar2)).Select
</code></pre>

blocks|key|3319156|text|由于.Rows结果被标记为由行组成，因此您可以"For+Each“单独处理每一行，如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3319157|Function+Attendance(rng+As+Range)+As+Long
Attendance+=+0
For+Each+rRow+In+rng.Rows
++++If+WorksheetFunction.Sum(rRow)+>+0+Then
++++++++Attendance+=+Attendance+%2B+1
++++End+If
Next
End+Function|code-block|syntax|javascript|3319158|我使用它来检查人员列表(不同行)的几个类别(不同列)中的任何一个类别的出席情况。|3319159|(当然，您可以使用.Columns对范围中的列执行"For+Each“。)|3319160|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

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

<pre><code>Function Attendance(rng As Range) As Long
Attendance = 0
For Each rRow In rng.Rows
 If WorksheetFunction.Sum(rRow) &gt; 0 Then
 Attendance = Attendance + 1
 End If
Next
End Function
</code></pre>

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

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 <code>range.Rows</code> (?) and <code>worksheet.Rows</code> 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 <code>*.Columns</code> properties also).

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

<pre><code> SET rng = wks.Rows(iStartRow, iEndRow)
</code></pre>

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 ( <code>rng.Rows(i)</code> ) and 2) return a count of the rows in a range ( <code>rng.Rows.Count</code> ). 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 <code>.Rows()</code> that we do not already get from <code>.Cells()</code> and <code>.Range()</code>? 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?

What does the Excel range.Rows property really do?

好了，我正在完成一个遗留的Excel-VBA应用程序的附加项目，我又一次遇到了神秘的range.Rows (?)和worksheet.Rows属性。有没有人知道这些属性到底是做什么的，它们应该提供给我什么？(注意:所有这些可能也适用于相应的*.Columns属性)。我真正希望能够使用它的是返回一系列行，如下所示：   ...

问Excel range.Rows属性的真正用途是什么？
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Excel range.Rows属性的真正用途是什么？EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Excel range.Rows属性的真正用途是什么？
EN