excel - 为什么 CountA 会产生额外的行

Question

每当我在第 4 行第 T 列填充任何数据时，我的代码都会产生一个额外的行。
如果我将第 4 行的 T 列留空，它将在第 5 行显示数据。

Sub All_N()
    Dim ws As Worksheet

    Sheets("sum").Activate
    Sheets("sum").Range("A5:T1048576").Select
    Selection.ClearContents
    Sheets("sum").Range("A5").Select

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "sum" Then
            cont1 = Application.WorksheetFunction.CountA(ws.Range("T3:T1048576")) + 2

            For a = 2 To cont1
                If ws.Cells(a, 20) = "N" Then
                    b = Application.WorksheetFunction.CountA(Sheets("sum").Range("T3:T1048576")) + 5
                    Sheets("sum").Cells(b, 1) = ws.Cells(a, 1)
                    Sheets("sum").Cells(b, 2) = ws.Cells(a, 2)
                    Sheets("sum").Cells(b, 18) = ws.Cells(a, 18)
                    Sheets("sum").Cells(b, 19) = ws.Cells(a, 19)
                    Sheets("sum").Cells(b, 20) = ws.Cells(a, 20)
                End If
            Next a
        End If
    Next
End Sub

Answer 1

我猜你想循环 所有使用的行在你所有的床单中
然后填写下一个空行 在您的“总和”表中。

要在“T”列中查找最后使用的行，
您可以使用 .Cells(.Rows.Count, "T").End(xlUp).Row

Option Explicit

Sub All_N()
    Dim wsSum As Worksheet
    Dim ws As Worksheet
    Dim a As Long, b As Long

    Set wsSum = ActiveWorkbook.Sheets("sum")
    With wsSum
        .Range("A5:T" & .Rows.Count).ClearContents
        b = 5
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name <> "sum" Then
                For a = 2 To ws.Cells(ws.Rows.Count, "T").End(xlUp).Row
                    If ws.Cells(a, "T") = "N" Then
                        .Cells(b, 1) = ws.Cells(a, 1)
                        .Cells(b, 2) = ws.Cells(a, 2)
                        .Cells(b, 18) = ws.Cells(a, 18)
                        .Cells(b, 19) = ws.Cells(a, 19)
                        .Cells(b, 20) = ws.Cells(a, 20)
                        b = b + 1
                    End If
                Next a
            End If
        Next
    End With
End Sub

没有必要 Select或 Activate任何东西，见 here .