excel - 每张 Excel 行数

Question

我目前正在使用下面的代码来计算每张工作表中的所有行，并打印在工作簿的主工作表上。现在，我正在尝试在该特定工作表的最后一行以及主工作表上打印每个工作表上使用的行数。

    Function Test_It()
    Dim printRow As Integer
    printRow = 2
    For Each Sheet In ThisWorkbook.Sheets
        Range("N" & printRow).Value = "Sheet Name:"
        Range("O" & printRow).Value = Sheet.Name
        Range("P" & printRow).Value = "Count:"
        Range("Q" & printRow).Value = CountMyRows(Sheet.Name)
        printRow = printRow + 1
    Next Sheet
End Function


Function CountMyRows(SName As String) As Long         '# where SName is the name of a sheet
    Dim RowCount As Long
    RowCount = ThisWorkbook.Worksheets(SName).UsedRange.Rows.Count - 1
    CountMyRows = RowCount
End Function

任何帮助表示赞赏，在此先感谢！

---

无效的代码

Sub LineCount()

    Dim ws As Worksheet
    Dim RowCount As Integer
    Dim countTotal As Long
    Dim myArray() As Variant

    RowCount= ActiveWorkbook.Worksheets.Count
    countTotal = RowCount
    ReDim myArray(1 To RowCount)

    For i = 1 To RowCount
        countTotal = ""
        myArray(1) = Worksheets(i).UsedRange.Rows.Count
        Debug.Print myArray(1)
        Range("A" & countTotal).Value = countTotal
    Next


End Sub

---

这是目标：

主表：

表 2：

表 3：

表 4：

---

我开始工作的最终代码

Sub LineCount()

Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")

For Each sht In ThisWorkbook.Worksheets
    dict(sht.Name) = sht.Cells(sht.Rows.Count, 1).End(xlUp).Row
    sht.Range("A" & Rows.Count).End(xlUp).Offset(2, 0) = "Rows Used: " & dict(sht.Name)
Next sht

With Sheet1
    .Range("A1").Resize(dict.Count).Value = Application.Transpose(dict.Keys)
    .Range("B1").Resize(dict.Count).Value = Application.Transpose(dict.Items)
End With


End Sub

现在唯一的问题是，当多次按下按钮时，它会将行数从 14 行重新计算到现在的 28 行，依此类推（取决于按下按钮的次数）。如何解决？

标签： excelvba

Answer 1

一些相当基本的东西可能是：

Sub Test()

Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")

For Each sht In ThisWorkbook.Worksheets
    dict(sht.Name) = sht.Cells(sht.Rows.Count, 1).End(xlUp).Row
Next sht

With Sheet1
    .Range("A2").Resize(dict.Count).Value = Application.Transpose(dict.Keys)
    .Range("B2").Resize(dict.Count).Value = Application.Transpose(dict.Items)
End With

End Sub

如果您想避免包含“主”表，请在属性上实施If条件，无论您的情况是什么。sht.Name