vba 获取对自动过滤表中下一个可见单元格的引用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26916945/
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
Get reference to next visible cell in autofiltered table
提问by daikomyo
I have a UserForm that sits on top of my spreadsheet and simply displays information from the row containing the currently selected cell. Buttons on the form allow the user to move up/down the spreadsheet row by row. For example, when the "next record" button is clicked, the code executed is something like:
我有一个用户窗体，它位于电子表格的顶部，仅显示包含当前选定单元格的行中的信息。表单上的按钮允许用户逐行向上/向下移动电子表格。例如，当点击“下一条记录”按钮时，执行的代码是这样的：
Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
LoadValues
I would like it to work even if the user filters the data and then loads the form. However, using the above code, it wants to loop through all cells, not just the ones still visible after filtering. I've seen solutions for immediately looping through only visible cells, e.g.,
即使用户过滤数据然后加载表单，我也希望它能够工作。但是，使用上面的代码，它希望遍历所有单元格，而不仅仅是过滤后仍然可见的单元格。我已经看到了立即循环仅可见单元格的解决方案，例如，
For Each viscell In rng.SpecialCells(xlCellTypeVisible)
...
Next viscell
So there seems like there should be a better, more direct way to do this than looping through all rows until I get to the next one with .Hidden = Falsebut I can't seem to figure out how to get a reference to "the next visible cell" in order to select it.
所以似乎应该有一种更好、更直接的方法来做到这一点，而不是循环遍历所有行直到我到达下一个，.Hidden = False但我似乎无法弄清楚如何获得对“下一个可见单元格”的引用为了选择它。
Any pointers would be greatly appreciated. Thanks.
任何指针将不胜感激。谢谢。
回答by Les S
I really struggled with this too and used Ryan's solution, albeit a little abbreviated, for my purposes.. This may be as brief as it gets. :)
我也真的为此苦苦挣扎，并使用了 Ryan 的解决方案，尽管有点简短，但出于我的目的.. 这可能会尽可能简短。:)
ActiveCell.EntireColumn.SpecialCells(xlCellTypeVisible).Find(What:="*", After:=ActiveCell).Activate
回答by Portland Runner
Here is one method using a simple loop until the next row down is visible.
这是使用简单循环直到下一行可见的一种方法。
Dim rng As Range
Set rng = ActiveCell
Dim n As Long: n = 1
Do Until rng.Offset(n, 1).Rows.Hidden = False
n = n + 1
Loop
rng.Offset(n, 1).Select
LoadValues
回答by Ryan Bradley
Here's a lightning-quick way to activate the first not-blank, visible cell in a filtered range. Just change 'Range("A1")' to whatever range you'd like to search after. Also consider whether you want xlByColumns or xlByRows.
这是激活过滤范围内第一个非空白可见单元格的闪电般快速的方法。只需将 'Range("A1")' 更改为您想要搜索的任何范围。还要考虑您是否需要 xlByColumns 或 xlByRows。
ActiveSheet.Cells.SpecialCells(xlCellTypeVisible).Find(What:="*", _
After:=ActiveSheet.Range("A1"), LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
I recommend using tables whenever possible, as they have variable named ranges built-in that are easy to reference.
我建议尽可能使用表格，因为它们具有易于引用的内置变量命名范围。
Here's another example, using tables. It's targeted to search a specific column, so it'll be even faster.
这是另一个使用表格的示例。它的目标是搜索特定的列，所以它会更快。
Just find and replace TABLE_NAME and COLUMN_NAME with your values.
只需找到并用您的值替换 TABLE_NAME 和 COLUMN_NAME 即可。
ActiveSheet.Range("TABLE_NAME[[#All], COLUMN_NAME]]").SpecialCells(xlCellTypeVisible).Find _
(What:="*", After:=ActiveSheet.Range("TABLE_NAME[[#Headers],[COLUMN_NAME]]"), _
LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
No loops required because it's built off of Excel's "Find" functionality.
不需要循环，因为它是基于 Excel 的“查找”功能构建的。
And just a tip,you can also use this to grab the data of such a cell on separate worksheet without activating/selecting it at all.
只是一个提示，您还可以使用它在单独的工作表上获取此类单元格的数据，而根本无需激活/选择它。
Just find and replace Dbl_EXAMPLE, SHEET_NAME, TABLE_NAME and COLUMN_NAME with your references.
只需找到并用您的参考文献替换 Dbl_EXAMPLE、SHEET_NAME、TABLE_NAME 和 COLUMN_NAME。
Dim Dbl_EXAMPLE as Double
Dbl_EXAMPLE = Sheets("SHEET_NAME").Range("TABLE_NAME[[#All], COLUMN_NAME]]").SpecialCells(xlCellTypeVisible).Find _
(What:="*", After:=Sheets("SHEET_NAME").Range("TABLE_NAME[[#Headers],[COLUMN_NAME]]"), _
LookIn:=xlFormulas, lookat:=xlPart, searchorder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Value2
Just replace ".Value2" at the end with whatever property you're looking for.
只需将末尾的“.Value2”替换为您要查找的任何属性。
Examples:
例子：
.Formula .Row .Column
.Formula .Row .Column
The list goes on
名单还在继续
