Excel VBA 过滤和复制粘贴数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23673177/
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
Excel VBA filtering and copy pasting data
提问by user3639909
Given a data set, that is lets say 10 columns. In column A i have date and in column B i have %. I want to filter for column A only 2014 data and for column B <70% and copy paste the filtered data into another worksheet. How do i go about writing the code for the next line to get to the first row of filtered data?
给定一个数据集，也就是说 10 列。在 A 列中我有日期，在 B 列中我有 %。我只想过滤 A 列的 2014 年数据和 B 列 <70% 的数据，并将过滤后的数据复制粘贴到另一个工作表中。我如何编写下一行的代码以到达过滤数据的第一行？
ActiveSheet.Range("$A:$AR17").AutoFilter Field:=5, Operator:= _
xlFilterValues, Criteria2:=Array(0, "12/28/2014")
ActiveSheet.Range("$A:$AR17").AutoFilter Field:=14, Criteria1:="<0.7" _
, Operator:=xlAnd
采纳答案by L42
Use Offsetmethod like this:
使用Offset方法如下：
ActiveSheet.Range("A1:AR1617").Offset(1, 0).SpecialCells(xlCellTypeVisible).Copy
Above code copies the filtered data excluding the header.
Is this what you're trying?
上面的代码复制过滤后的数据，不包括标题。
这是你正在尝试的吗？
回答by jaybee3
ActiveSheet.Range("$A:$AR17").specialcells(xlcelltypevisible).copy sheets("Sheet2").range("A1")
Copies only the visible/filtered cells to a new worksheet as the first part of your question requests.
作为问题请求的第一部分，仅将可见/过滤的单元格复制到新工作表。
To get the first line of data, presuming there is data and you have headers in line 1, and paste that to a new sheet's next available row.
要获取第一行数据，假设有数据并且您在第 1 行有标题，然后将其粘贴到新工作表的下一个可用行。
Activesheet.range("A1").end(xldown).entirerow.copy sheets("Sheet2").range("A65536").end(xlup).offset(1,0)
And a loop solution, that will copy data from sheet1 to sheet2, providing that column A always contains data (will be a bit slow on very large data sets):
还有一个循环解决方案，它将数据从 sheet1 复制到 sheet2，前提是 A 列始终包含数据（在非常大的数据集上会有点慢）：
Sub copyFilteredData(ByVal iYear As Integer, ByVal dPercent As Double)
Dim rCell As Range
For Each rCell In Sheet1.Range("A1:A65536").SpecialCells(xlCellTypeConstants)
If Year(rCell.Offset(0, 4).Value) = iYear And rCell.Offset(0, 13).Value < dPercent Then
rCell.EntireRow.Copy Sheet2.Range("A65536").End(xlUp).Offset(1, 0)
End If
Next rCell
End Sub
Call using:
调用使用：
copyfilteredData 2014,0.7
