计算多个Excel表单中的行数，并将值返回到“摘要”片 [英] Count number of rows in multiple Excel sheets and return values to a "summary" sheet

问题描述

在摘要表中，我想总结每个其他工作表上使用了多少行。

我认为多次调用的函数是最有效的。

如果是所以，然后我觉得它开始像...

 函数CountMyRows（SName）＃其中SName是表单的名称
 Dim rowCount As Integer 
 rowCount = Worksheets（SName）。Rows.Count 
  

我从正确的方向开始吗？

解决方案

将以下两个函数放入工作簿中要计数的模块中所有工作表中使用的行。我使用Function'Test_it'来抓取工作簿中的每个工作表，然后通过传递一个工作表名称来调用'CountMyRows'。要测试它，将光标放在Test_It功能中，然后按F5。您将在立即窗口中看到结果。注意，您的代码必须更改，因为（1）它计算了工作表中的总行数 - 未使用的行数;和（2）整数可能太小...

 功能Test_It（）
在ThisWorkbook中的每个工作表.Sets 
 Debug.Print Sheet.Name& vbTab& CountMyRows（Sheet.Name）
下一张表
结束函数
 
 
函数CountMyRows（SName）As Long'＃其中SName是工作表的名称
 Dim rowCount As Long 
 rowCount = Worksheets（SName）.UsedRange.Rows.Count 
 CountMyRows = rowCount 
结束函数
  

I have an Excel workbook with 10 plus sheets.

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?

解决方案

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.

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

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

这篇关于计算多个Excel表单中的行数，并将值返回到“摘要”片的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！