vba 使用过滤器选项将值从一张纸复制到另一张纸
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21228254/
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
Copy values from one sheet into another using Filter option
提问by user3212324
I am trying to copy the values from one Excel sheet into another using Filter option. For example I have used only ten records, but in real time I am not sure the data that will be present. Also, I need to know the first cell value after a filter. For example, if I use filter the first value is reflecting as B4 and next time it is showing B6. I need to select that also dynamically using macro.
我正在尝试使用过滤器选项将值从一个 Excel 工作表复制到另一个工作表中。例如，我只使用了 10 条记录，但实时我不确定将出现的数据。另外，我需要知道过滤器后的第一个单元格值。例如，如果我使用过滤器，第一个值反映为 B4，下次它显示为 B6。我还需要使用宏动态选择它。
ActiveSheet.Range("$A:$BG").AutoFilter Field:=2, Criteria1:="2"
Range("B5:BG5").Select
The above code should be modified. Instead of $BG$10 it should be the number of rows, then Instead of B5:BG5 it must be the first cell after filter.
应该修改上面的代码。它应该是行数而不是 $BG$10，那么它必须是过滤器后的第一个单元格而不是 B5:BG5。
回答by Dmitry Pavliv
Try following code:
尝试以下代码：
Sub test()
Dim lastRow As Long, firstVisibleRow As Long
ActiveSheet.AutoFilterMode = False
'find last non empty row number in column A'
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
'apply filter'
Range("$A:$BG$" & lastRow).AutoFilter Field:=2, Criteria1:="2"
On Error GoTo errHandler
'find first visible row number in the filtered range, if there is no rows matching the filter criteria, we'll get message from the MsgBox'
firstVisibleRow = Range("$A:$BG$" & lastRow).SpecialCells(xlCellTypeVisible).Row
On Error GoTo 0
'select range'
Range("B" & firstVisibleRow & ":BG" & firstVisibleRow).Select
Exit Sub
errHandler:
MsgBox "There is no rows matching the filter criteria"
End Sub
回答by L42
Try this:
尝试这个：
Dim rngToFilter As Range
With ActiveSheet
.AutoFilterMode = False 'to make sure no filter is applied yet
Set rngToFilter = .Range("A1", .Range("BG" & Rows.Count).End(xlUp)) 'set the dynamic range
rngToFilter.AutoFilter Field:=2, Criteria1:="2" 'apply the filter
rngToFilter.Resize(.Range("BG" & Rows.Count).End(xlUp).Row - 1).Offset(1, 0).SpecialCells(xlCellTypeVisible).Select 'Offset 1 row to exclude the header, resize to select the first row only.
End With
Above code selects all the items that are filtered.
I you want so select only the 1st item filtered, then use below.
上面的代码选择了所有被过滤的项目。
我想要所以只选择过滤的第一个项目，然后在下面使用。
Sub Sample()
Dim rngToFilter As Range, rngFilter As Range
Dim i As Integer
With ActiveSheet
.AutoFilterMode = False 'to make sure no filter is applied yet
Set rngToFilter = .Range("A1", .Range("BG" & Rows.Count).End(xlUp)) 'set the dynamic range
rngToFilter.AutoFilter Field:=2, Criteria1:="2" 'apply the filter
Set rngFilter = rngToFilter.Resize(.Range("BG" & Rows.Count).End(xlUp).Row - 1).Offset(1, _
0).SpecialCells(xlCellTypeVisible)
rngToFilter.Resize(.Range("BG" & Rows.Count).End(xlUp).Row - _
(rngFilter.Cells.Count / rngFilter.Columns.Count)).Offset(1, _
0).SpecialCells(xlCellTypeVisible).Select
End With
End Sub
No error handler yet.
I leave it to you. :D
还没有错误处理程序。
我把它留给你。:D
