使用 VBA 过滤后选择表中的前 n 行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18707693/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select top-n rows in table after filter with VBA
提问by user2762926
It seems that applying filter to a table has destroyed my understanding of how to handle this.
似乎将过滤器应用于表破坏了我对如何处理这个问题的理解。
I have a table with multiple columns. I'm going to filter on one of the columns and sort on another. After that, I want to select/copy the first 10 rows of specific columns of what's filtered into another table.
我有一个多列的表。我将过滤其中一列，然后对另一列进行排序。之后，我想选择/复制过滤到另一个表的特定列的前 10 行。
I could easily do this before filters. I need the first 10 rows AFTER the filter is applied. I'm not seeing how to choose the 10th row AFTER a filter.
我可以在过滤器之前轻松地做到这一点。我需要应用过滤器后的前 10 行。我没有看到如何在过滤器之后选择第 10 行。
Can anyone point me to a VBA reference that explains how to do this? Do I need to use SQL to do this? Am I over thinking this and making it too complicated?
任何人都可以给我指出解释如何执行此操作的 VBA 参考资料吗？我是否需要使用 SQL 来执行此操作？我是不是想多了，让它太复杂了？
回答by Andy G
The following works to select the first 10 visible cells of column F, after filtering is applied. You'll need start at F2 if you want to exclude the header-cell.
在应用过滤后，以下工作选择 F 列的前 10 个可见单元格。如果要排除标题单元格，则需要从 F2 开始。
Sub TenVisible()
Dim rng As Range
Dim rngF As Range
Dim rng10 As Range
Set rngF = Range("F:F").SpecialCells(xlCellTypeVisible)
For Each rng In Range("F:F")
If Not Intersect(rng, rngF) Is Nothing Then
If rng10 Is Nothing Then
Set rng10 = rng
Else
Set rng10 = Union(rng10, rng)
End If
If rng10.Cells.Count = 10 Then Exit For
End If
Next rng
Debug.Print rng10.Address
'.. $F:$F,$F:$F,$F:$F,$F,$F:$F,$F
rng10.Select
End Sub
The following is one of a number of ways to select from F2 downwards (assuming the UsedRangestarts from row 1):
以下是从 F2 向下选择的多种方法之一（假设UsedRange从第 1 行开始）：
Set rngF = Range("F2", Cells(ActiveSheet.UsedRange.Rows.Count, _
Range("F2").Column)).SpecialCells(xlCellTypeVisible)
回答by AjV Jsy
For what it's worth, seeing as this is one of the first search results for this kind of issue -
after filtering a table on a named column like this :
对于它的价值，因为这是此类问题的第一个搜索结果之一 -
在像这样过滤命名列上的表之后：
Worksheets("YourDataSheet").ListObjects("Table_Name").Range.AutoFilter _
field:=Worksheets("YourDataSheet").ListObjects("Table_Name").ListColumns("ColumnName").Index, _
Criteria1:="FilterFor..."
... I was then able to copy the resulting single visible row to another sheet using :
...然后我能够使用以下方法将生成的单个可见行复制到另一个工作表：
Worksheets("YourDataSheet").Range("Table_Name").SpecialCells(xlCellTypeVisible).Range("A1").EntireRow.Copy _
Destination:=Range("AnotherSheet!$A").EntireRow
So that's one way to refer to visible rows after the filtering. HTH.
所以这是在过滤后引用可见行的一种方法。哈。
