使用 Excel VBA 选择结果会有所不同的过滤范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23942314/
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
Using Excel VBA to Select a filtered range where results will vary
提问by user3689031
I am working with a spreadsheet to produce a report. The data will vary depending on the department for which I am reporting. I need to select the records in column A that are the result of a filter and paste them in column B, once pasted I need to clear the cells in column A then remove the filter. I have been able to accommodate the variable criteria for the filter; however, my problem is with selecting the variable range. The data to be selected will never start in cell A2, any cell after that is fair game. How do I select a range that varies? How do I have the User interact with the macro to select the cells?
我正在使用电子表格来生成报告。数据将因我报告的部门而异。我需要选择 A 列中作为过滤器结果的记录并将它们粘贴到 B 列中，粘贴后我需要清除 A 列中的单元格，然后删除过滤器。我已经能够适应过滤器的可变标准；但是，我的问题是选择变量范围。要选择的数据永远不会从单元格 A2 开始，之后的任何单元格都是公平游戏。如何选择变化的范围？我如何让用户与宏交互以选择单元格？
This works for me to filter by all my variables.
这对我有用，可以按我的所有变量进行过滤。
Cells.Select
Selection.AutoFilter
ActiveSheet.Range("$A:$F000").AutoFilter Field:=1, Criteria1:=Array( _
"1000", "1001", "1005", "ZBIL", "1002", "1003", "1004", "1006", "1007", "1008", "1009", "AOMS", "ASPS", "NATL", "ZCON", "ZREP"), Operator:=xlFilterValues
On Error Resume Next
I am using the following to select the range, the first line works to select the data to be copied (although it still selects the cell A1 and I would prefer not to have that cell selected). The second line returns an error "Object doesn't support this property or method. I have tried range select ToRight however I end up with the entire sheet selected.
我正在使用以下内容来选择范围，第一行用于选择要复制的数据（尽管它仍然选择单元格 A1 并且我不想选择该单元格）。第二行返回错误“对象不支持此属性或方法。我尝试了范围选择 ToRight，但最终选择了整个工作表。
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection(Selection, Selection.xlToRight).Select
Selection.FillRight
I appreciate any assistance that is available.
我感谢任何可用的帮助。
采纳答案by Dave.Gugg
It might not be the most efficient solution, but you can loop through all the cells/rows and check if the filter hid them:
这可能不是最有效的解决方案，但您可以遍历所有单元格/行并检查过滤器是否隐藏了它们：
Dim lastrow As Integer
Dim i As Integer
With ActiveSheet
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 3 To lastrow
If .Rows(i).Hidden = False Then
.Cells(i, 2).Value = .Cells(i, 1).Value
.Cells(i, 1).Value = ""
End If
Next i
End With
