vba 确定范围内的行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/114493/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Determine the number of rows in a range
提问by SpyJournal
I know the range name of the start of a list - 1column wide and xrows deep.
我知道列表开头的范围名称 -1列宽和x行深。
How do I calculate x?
我如何计算x？
There is more data in the column than just this list. However, this list is contiguous - there is nothing in any of the cells above or below or either side beside it.
列中的数据不仅仅是这个列表。但是，此列表是连续的 - 在其上方或下方或两侧的任何单元格中都没有任何内容。
采纳答案by GSerg
Function ListRowCount(ByVal FirstCellName as String) as Long
With thisworkbook.Names(FirstCellName).RefersToRange
If isempty(.Offset(1,0).value) Then
ListRowCount = 1
Else
ListRowCount = .End(xlDown).row - .row + 1
End If
End With
End Function
But if you are damn sure there's nothing around the list, then just thisworkbook.Names(FirstCellName).RefersToRange.CurrentRegion.rows.count
但是，如果您确定列表中没有任何内容，那么只需 thisworkbook.Names(FirstCellName).RefersToRange.CurrentRegion.rows.count
回答by
Sheet1.Range("myrange").Rows.Count
回答by user1371038
Why not use an Excel formula to determine the rows? For instance, if you are looking for how many cells contain data in Column A use this:
为什么不使用 Excel 公式来确定行？例如，如果您要查找 A 列中有多少个单元格包含数据，请使用以下命令：
=COUNTIFS(A:A,"<>")
=COUNTIFS(A:A,"<>")
You can replace <> with any value to get how many rows have that value in it.
您可以将 <> 替换为任何值以获取其中包含该值的行数。
=COUNTIFS(A:A,"2008")
=COUNTIFS(A:A,"2008")
This can be used for finding filled cells in a row too.
这也可用于在一行中查找填充的单元格。
回答by Jon Fournier
You can also use:
您还可以使用：
Range( RangeName ).end(xlDown).row
to find the last row with data in it starting at your named range.
从您的命名范围开始查找包含数据的最后一行。
回答by wcm
I am sure that you probably wanted the answer that @GSerg gave. There is also a worksheet function called rowsthat will give you the number of rows.
我相信您可能想要@GSerg 给出的答案。还有一个工作表函数rows可以为您提供行数。
So, if you have a named data range called Datathat has 7 rows, then =ROWS(Data)will show 7 in that cell.
因此，如果您有一个名为Data7 行的命名数据范围，那么=ROWS(Data)将在该单元格中显示 7。
回答by SpyJournal
That single last line worked perfectly @GSerg.
最后一行很完美@GSerg。
The other function was what I had been working on but I don't like having to resort to UDF's unless absolutely necessary.
另一个功能是我一直在研究的功能，但除非绝对必要，否则我不喜欢不得不求助于 UDF。
I had been trying a combination of excel and vba and had got this to work - but its clunky compared with your answer.
我一直在尝试将 excel 和 vba 结合使用，并且已经让它起作用了 - 但与您的答案相比，它很笨拙。
strArea = Sheets("Oper St Report CC").Range("cc_rev").CurrentRegion.Address
cc_rev_rows = "=ROWS(" & strArea & ")"
Range("cc_rev_count").Formula = cc_rev_rows
