使用 Excel VBA 中的自动过滤器从 Excel 工作表中剪切粘贴数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7117028/
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
Cut Paste data from excel sheet using Autofilter in Excel VBA
提问by Sunny D'Souza
Current i have an excel with roughly 200000+ records and i need to filter data based on a column. The column has around 5 values and i need to filter out 2 values in one sheet and the rest 3 to remain in the same sheet.
目前我有一个大约有 200000 多条记录的 excel，我需要根据列过滤数据。该列有大约 5 个值，我需要在一张纸中过滤掉 2 个值，其余 3 个值保留在同一张纸中。
Now instead of using cell by cell comparison to check whether the value of the cell falls in any of the above 2 values and then cut paste the row into another sheet. This wouldn't work with 200k+ records and simply hangs,.
现在而不是使用逐个单元格比较来检查单元格的值是否属于上述 2 个值中的任何一个，然后将该行剪切粘贴到另一个工作表中。这不适用于 200k+ 条记录并且只是挂起。
Instead am planning to take the auto filter method. I tried using the 'Record macro' feature, but the problem is that it gives me some error like
相反，我计划采用自动过滤方法。我尝试使用“记录宏”功能，但问题是它给了我一些错误，例如
"Excel cannot create or use the data range reference because its too complex.Try one of the following Use data that can be selected in rectangle Use data from the same sheet"
“Excel 无法创建或使用数据范围引用，因为它太复杂。尝试以下方法之一使用可以在矩形中选择的数据使用来自同一工作表的数据”
Moreover how to copy paste only the filtered values to another sheet? If I try to copy paste directly or special paste as 'values' then also even the hidden rows get copy pasted.
此外，如何仅将过滤后的值复制粘贴到另一个工作表？如果我尝试直接复制粘贴或特殊粘贴作为“值”，那么即使隐藏的行也会被复制粘贴。
Below is the macro code i have been tampering around with
下面是我一直在篡改的宏代码
Sub Macro34()
'
' Macro34 Macro
'
'
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A:$T335").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
There might be some junk lines of code above as its generated using the 'record macro' feature.
上面可能有一些垃圾代码行，因为它是使用“记录宏”功能生成的。
Could someone please help me. The problem is the amount of data present in excel. Cant excel not handle this much data in VBA? Am using Excel 2007
有人可以帮助我。问题是excel中存在的数据量。excel不能在VBA中处理这么多数据吗？我正在使用 Excel 2007
回答by variant
Here's your code cleaned up:
这是您清理的代码：
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
The key is that SpecialCells part.
关键是 SpecialCells 部分。
Now, as much as I love a good autofilter copy/paste, when you're dealing with that much data, you might want to look into using ADO, which would allow you to query your Excel worksheet using SQL.
现在，尽管我喜欢一个好的自动过滤器复制/粘贴，但当你处理这么多数据时，你可能想考虑使用 ADO，它允许你使用 SQL 查询你的 Excel 工作表。
A good overview of ADO in VBA is provided here: http://www.xtremevbtalk.com/showthread.php?t=217783.
这里提供了 VBA 中 ADO 的一个很好的概述：http://www.xtremevbtalk.com/showthread.php?t= 217783。
回答by Mark Hymanson
In the 1st empty column to the right of your data insert a formula that tests for your criteria: e.g.
在数据右侧的第一个空列中插入一个测试您的标准的公式：例如
=if(or(a2=242,a2-244),"Move","Keep")
then in your macro, sort the whole 200,000 line data set by that column before you attempt the filter and cut visible code described in answer1.
然后在您的宏中，在尝试过滤器并剪切 answer1 中描述的可见代码之前，对该列设置的整个 200,000 行数据进行排序。
This will make the block of data to be cut-n-pasted one contiguous range. This should get around the 'data range too complex' error.
这将使数据块被剪切和粘贴到一个连续的范围内。这应该可以解决“数据范围太复杂”错误。
