使用 Excel VBA 中的自动筛选从 Excel 工作表中剪切粘贴数据

Question

目前我有一个包含大约 200000 多条记录的 Excel，我需要根据列过滤数据。该列有大约 5 个值，我需要过滤掉一张纸中的 2 个值，其余 3 个值保留在同一张纸中。

现在，不要使用逐个单元格比较来检查单元格的值是否属于上述两个值中的任何一个，然后将该行剪切粘贴到另一张纸中。这不适用于 200k 以上的记录，并且会挂起。

相反，我计划采用自动过滤方法。我尝试使用“记录宏”功能，但问题是它给了我一些错误，例如

“Excel 无法创建或使用数据范围引用，因为它太复杂了。请尝试以下操作之一 使用可以在矩形中选择的数据 使用同一张纸中的数据”

此外，如何仅复制粘贴过滤后的值到另一张纸？如果我尝试直接复制粘贴或特殊粘贴为“值”，那么即使是隐藏的行也会被复制下面

是我一直在篡改的宏代码

    Sub Macro34()
    '
    ' Macro34 Macro
    '

    '
        Rows("1:1").Select
        Selection.AutoFilter
        ActiveSheet.Range("$A$1:$T$81335").AutoFilter Field:=6, Criteria1:="=242", _
            Operator:=xlOr, Criteria2:="=244"
        Cells.Select
        Selection.Copy
        ActiveWindow.SmallScroll Down:=21
        Sheets("Sheet2").Select
        ActiveWindow.SmallScroll Down:=-18
        Range("A1").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Selection.ClearContents
        Range("A1").Select
        Sheets("Sheet1").Select
        Selection.Copy
        Sheets("Sheet2").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("Sheet2").Select
        ActiveWindow.SmallScroll Down:=93
        Sheets("Sheet1").Select
        ActiveWindow.SmallScroll Down:=-9
        ActiveWindow.ScrollRow = 1
        Rows("1:1").Select
        Application.CutCopyMode = False
        Selection.AutoFilter
    End Sub

，上面可能有一些垃圾代码行，

有人可以帮助我吗？问题是Excel中存在的数据量。无法处理这么多数据我使用的是 Excel 2007

Answer 1

这是清理后的代码：

Sub Macro34()

    ' Turn off autofiltering
    If ActiveSheet.AutoFilterMode Then ActiveSheet.AutoFilterMode = False

    ' Turn it back on
    Rows(1).AutoFilter

    ' Set the autofiltering conditions
    Rows(1).AutoFilter Field:=6, _
        Criteria1:="=242", _
        Operator:=xlOr, _
        Criteria2:="=244"

    ' Copy only the relevant range
    Range("A1", _
          Cells(65536, Cells(1, 256).End(xlToLeft).Column).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy

    ' Paste the data into Sheet2 (assuming that it exists)
    Sheets("Sheet2").Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
    Application.CutCopyMode = False

End Sub

关键是 SpecialCells 部分。

现在，尽管我非常喜欢良好的自动筛选复制/粘贴功能，但当您处理大量数据时，您可能需要考虑使用 ADO，它允许您使用 SQL 查询 Excel 工作表。

这里提供了 VBA 中 ADO 的详细概述：http://www.xtremevbtalk.com/ showthread.php?t=217783。

Answer 2

在数据右侧的第一个空列中插入一个测试您的条件的公式：例如

=if(or(a2=242,a2-244),"Move","Keep")

，然后在宏中，在尝试过滤并剪切答案1中描述的可见代码之前，对该列对整个 200,000 行数据集进行排序。

这将使数据块被剪切粘贴到一个连续的范围。这应该可以解决“数据范围太复杂”错误。