在过滤数据中选择一个范围 - Excel VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44504086/
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
Selecting a Range in filtered data - Excel VBA
提问by Alex
I have a macro that AutoFilters a range & filters for criteria successfully. I am looking to copy-visible this resulting range, and paste it elsewhere, but am having trouble selecting this filtered-range. Row 1 of my data is a header.
我有一个宏，可以成功自动筛选范围和筛选条件。我希望将此结果范围复制可见，并将其粘贴到其他地方，但无法选择此过滤范围。我的数据的第 1 行是标题。
Here is the code snippet, applied to the filtered data:
这是应用于过滤数据的代码片段：
Set mainsheet = Workbooks("MyFile.xlsm").Sheets("Main")
Range("A1").Select
With mainsheet
.Range(.Cells(Selection.Row + 1, 1), .Cells(Selection.Row + 1, 47)).Select
End With
I figured this would move the cursor down to the next visible cell, but it actually moves down to cell A2 (which is filtered out).
我认为这会将光标向下移动到下一个可见单元格，但它实际上向下移动到单元格 A2（已被过滤掉）。
I need to select the first row of data not including my header, so I can xlDown and copy, but I can't figure out how to increment downward. Any tips?
我需要选择不包括我的标题的第一行数据，所以我可以 xlDown 并复制，但我不知道如何向下递增。有小费吗？
采纳答案by Alex
I've just realized that utilizing SpecialCells(xlCellTypeVisibile).Select would mitigate this issue, as the filtered cells are obviously not visibile
我刚刚意识到使用 SpecialCells(xlCellTypeVisibile).Select 可以缓解这个问题，因为过滤的单元格显然不可见
回答by paul bica
Use this notation
使用这个符号
With mainsheet.UsedRange
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy
End With
.Offset(1)moves the entire selection one row down (exclude the headers)
.Offset(1)将整个选择向下移动一行（不包括标题）
.Resize(.Rows.Count - 1)removes the last (extra) row selected by the offset
.Resize(.Rows.Count - 1)删除由偏移量选择的最后（额外）行
