excel - 如何在 VBA 中使用 SpecialCells(xlCellTypeVisible) 仅从范围中选择行？

Question

我正在尝试从表中复制过滤后的数据并将其粘贴到另一个工作簿中。到目前为止，此过程有效，但复制数据的行数超过了该范围的实际行数。它似乎正在计算复制的过滤表中每列的行数，因为当我粘贴值时，我还会看到额外的空行。

我试过.DataBodyRange.SpecialCells(xlCellTypeVisible).Rows.Count但给了我错误。关于“范围不起作用，因为它与现有表不对齐......”

 'Filter the data based on the field and criteria
    With SourceListObject
        If Not Id = "" Then
            .Range.AutoFilter Field:=2, Criteria1:=Id
        End If
        
        'Get the total visible cell count after filtering
        SourceDataRowsCount = .DataBodyRange.SpecialCells(xlCellTypeVisible).Count

    End With
    
    
    'Copy the filtered data to the table
    With DestListObject
        'Get the count of the rows in the table (starts with 2 empty rows - need to figure out a way to either
        'have no row and add to the table or delete the rows after adding values to the rows
        'Then set the size of the range based on the source range count
        TargetDataRowsCount = .DataBodyRange.Rows.Count
        .Resize .Range.Resize(SourceDataRowsCount, .Range.Columns.Count)
        
        'Paste the values in the first row
        SourceListObject.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy
        .DataBodyRange.Cells(1, 1).PasteSpecial xlPasteValues
        
    End With
    
    
    'Clear the filter
    With SourceListObject
    
        .Range.AutoFilter Field:=2
        
    End With

我看到像这样的空行。这是来自编造的数据：

我在这里做错了什么？

标签： excelvbauserform