vba 计算多个 Excel 工作表中的行数并将值返回到“摘要”工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22694463/
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
Count number of rows in multiple Excel sheets and return values to a "summary" sheet
提问by thisisme
I have an Excel workbook with 10 plus sheets.
我有一个包含 10 多张工作表的 Excel 工作簿。
On the "Summary" sheet I want to summarise how many rows are used on each of the other sheets.
在“摘要”表上，我想总结其他每个表上使用了多少行。
I'm thinking that a Function that gets called multiple times is the most efficient.
我认为被多次调用的函数是最有效的。
If this is so, then I think it starts something like;
如果是这样，那么我认为它开始于；
Function CountMyRows(SName) # where SName is the name of a sheet
Dim rowCount As Integer
rowCount = Worksheets("SName").Rows.Count
Am I starting in the right direction?
我是否朝着正确的方向开始？
回答by Dmitry Pavliv
I want to summarise how many rows are used on each of the other sheets
我想总结一下其他每张纸上使用了多少行
you're looking for UsedRangeproperty:
您正在寻找UsedRange属性：
Function CountMyRows(SName As String) As Long
CountMyRows = ThisWorkbook.Worksheets(SName).UsedRange.Rows.Count
End Function
note, that I'm using Worksheets(SName)without quotes and also it's more reliable to use Longtype for storing rows count, because max value of Integeris only 32767.
请注意，我使用的是Worksheets(SName)不带引号的，而且使用Longtype 来存储行数更可靠，因为最大值Integer仅为32767.
回答by Wayne G. Dunn
Place the following two Functions into a module in the Workbook where you want to count the used rows in all worksheets. I used Function 'Test_it' to grab every sheet in the workbook, then call 'CountMyRows' by passing a Sheet name. To test it, place the cursor inside the 'Test_It' function, then press F5. You will see the results in the immediate window.
将以下两个函数放入工作簿中要计算所有工作表中使用的行的模块中。我使用函数“Test_it”来抓取工作簿中的每个工作表，然后通过传递工作表名称来调用“CountMyRows”。要测试它，请将光标放在“Test_It”函数内，然后按 F5。您将在即时窗口中看到结果。
Note your code had to be changed because (1) it counted total rows in sheet - not used rows; and (2) an Integer may be too small ...
请注意，您的代码必须更改，因为 (1) 它计算了工作表中的总行数 - 未使用的行数；(2) Integer 可能太小了...
Function Test_It()
For Each Sheet In ThisWorkbook.Sheets
Debug.Print Sheet.Name & vbTab & CountMyRows(Sheet.Name)
Next Sheet
End Function
Function CountMyRows(SName) As Long '# where SName is the name of a sheet
Dim rowCount As Long
rowCount = Worksheets(SName).UsedRange.Rows.Count
CountMyRows = rowCount
End Function
