过滤后VBA选择可见单元格 [英] VBA selecting visible cells after filtering

问题描述

以下代码应用过滤器并在将一些过滤器应用于表格后选择 B 列中的前 10 项.我已经将它用于许多不同的过滤选择，但我遇到了我的过滤器组合之一的问题.

The following code applies filters and selects the top 10 items in column B after some filters are applied to the table. I have been using this for many different filtered selection, but I came across a problem with one of my filter combinations.

我发现当过滤后 B 列中只有一个项目时，它不会复制那个单元格 - 而是复制整行，似乎是一个奇怪的选择.

I found that when there is only one item in column B after filtering, it doesn't copy that one cell - instead it copies the entire row and seems to be a strange selection.

当我手动向此过滤器添加一项(共 2 项)时，它会很好地复制它.关于为什么只有一个项目时此代码不起作用的任何想法?

When I manually add one more item to this filter (total 2), then it copies it fine. Any ideas on why this code won't work when there is only one item?

Sub top10()

Dim r As Range, rC As Range
Dim j As Long

'Drinks top 10
Worksheets("OLD_Master").Columns("A:H").Select
Selection.sort Key1:=Range("H1"), Order1:=xlDescending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

Worksheets("OLD_Master").Range("A:H").AutoFilter Field:=4, Criteria1:=Array(     _
    "CMI*"), Operator:= _
    xlFilterValues
Worksheets("OLD_Master").Range("A:H").AutoFilter Field:=5,   Criteria1:="Drinks"

Set r = Nothing
Set rC = Nothing
j = 0

Set r = Range("B2", Range("B" &     Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)

For Each rC In r
    j = j + 1
    If j = 10 Or j = r.Count Then Exit For
Next rC

Range(r(1), rC).SpecialCells(xlCellTypeVisible).Copy

Worksheets("For Slides").Range("P29").PasteSpecial
Worksheets("OLD_Master").ShowAllData

End Sub

推荐答案

Rory 很有帮助地指出:

Rory helpfully points out:

如果您只将特殊单元格应用于一个单元格，它实际上会应用于工作表的整个使用范围.

If you apply Specialcells to only one cell, it actually applies to the entire used range of the sheet.

现在我们知道问题是什么了，我们可以避免它！使用 SpecialCells 的代码行:

Now we know what the problem is, we can avoid it! The line of code where you use SpecialCells:

Set r = Range("B2", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible)

相反，先设置范围，测试它是否只包含一个单元格，然后继续...

Instead, set the range first, test if it only contains one cell, then proceed...

Set r = Range("B2", Range("B" & Rows.Count).End(xlUp))
' Check if r is only 1 cell
If r.Count = 1 Then
    r.Copy
Else ' Your previous code
    Set r = r.SpecialCells(xlCellTypeVisible)
    For Each rC In r
        j = j + 1
        If j = 10 Or j = r.Count Then Exit For
    Next rC
    Range(r(1), rC).SpecialCells(xlCellTypeVisible).Copy
End If

注意，您假设还有 一行 仍然可见.可能是 .End(xlUp) 如果没有可见数据，选择了第 1 行，您可能也想检查这是哪一行！

Note, you're assuming there is even one row still visible. It might be that the .End(xlUp) selects row 1 if there is no visible data, you may want to check which row this is first too!

旁白:你真的应该完全限定你的范围，即而不是

Aside: You really should be fully qualifying your ranges, i.e. instead of

 Set r = Range("B2")

你应该使用

Set r = ThisWorkbook.Sheets("MySheet").Range("B2")

这将在未来为您节省一些令人困惑的错误.您可以使用一些快捷方式，例如使用 With 块或声明工作表对象来保存重复.

This will save you some confusing errors in future. There are shortcuts you can take, for example saving repetition using With blocks or declaring sheet objects.

' using With blocks
With ThisWorkbook.Sheets("MySheet")
    Set r = .Range("B2")
    Set s = .Range("B3")
    ' ...
End With

' Using sheet objects
Dim sh as Worksheet
Set sh = ThisWorkbook.Sheets("MySheet")
Set r = sh.Range("B2")

这篇关于过滤后VBA选择可见单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！