复制过滤范围内的单元格，其中只有一行 [英] Copy cell from filtered range, where is only one row

问题描述

我正在尝试从过滤范围内的列中复制数据.过滤的行数始终是不同的.第一行是标题.

I'm trying to copy data from column in filtered range. The number of filtered rows is always different. First row is header.

我正在使用这个

ThisWorkbook.Sheets(1).Range("N2:N" & ThisWorkbook.Sheets(1).Range("N" & ThisWorkbook.Sheets(1).Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy

当过滤的行数大于1时，此方法很好用.但是，当只有一个过滤的行(应为N2:N2)时，它将复制整个使用范围.

This works fine, when the number of filtered rows is bigger than 1. But when there's only one filtered row (and it should be N2:N2), it will copy the whole used range.

感谢您的咨询.

推荐答案

您可以进行测试以确保在复制范围之前，有比标题行更多的可见行.将范围设置为变量，然后计算范围内可见单元的数量，以确保有要复制的行.另外，正如JvdV指出的那样，您应该使用 N1 作为开始.复制范围内的可见单元格时，应始终使用 Offset 来确保不复制标题行，并使用 Resize 来确保不复制以下位置的空白单元格底部由于偏移.这是如何完成任务的基本示例.

You can test to ensure that there are more visible rows then the Header Row before copying your range. Set your range to a variable, then count the number of visible cells in the range to ensure there are rows to copy. Also, as JvdV pointed you should use N1 as the start. When copying visible cells in a range you should always use Offset to ensure you don't copy the Header Row, and Resize to ensure you don't copy the blank cell at the bottom due to the Offset. Here is a basic example of how to accomplish your task.

Dim rng As Range
Set rng = ThisWorkbook.Sheets(1).Range("D1:D" & ThisWorkbook.Sheets(1).Range("D" & ThisWorkbook.Sheets(1).Rows.Count).End(xlUp).Row)
    If rng.SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
        rng.Resize(rng.Cells.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).Copy
    End If

这篇关于复制过滤范围内的单元格，其中只有一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！