vba 在下面找到第一个可见单元格并获取参考
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23081833/
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
Find first visible cell below and get reference
提问by Seya
I'm trying to select first visiblecell after header while using autofilter.
我正在尝试在使用自动过滤器时选择标题后的第一个可见单元格。
If First.AutoFilter.Range.Columns(1).SpecialCells(xlVisible).Count - 1 > 0 Then
Range("A1").Select
Do
ActiveCell.Offset(1, 0).Select
Loop While ActiveCell.EntireRow.Hidden = True
But in next step I need to operate with this cell, so I need its row and column number. How could I get it? (without using lastrow and lastcolumn)
但是下一步我需要对这个单元格进行操作，所以我需要它的行号和列号。我怎么能得到它？（不使用 lastrow 和 lastcolumn）
回答by brettdj
You could try this (first three lines used as sample code)
你可以试试这个（前三行用作示例代码）
Updated for user comment
更新用户评论
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = [a1:a10]
rng1.AutoFilter Field:=1, Criteria1:="4"
If rng1.SpecialCells(xlVisible).Count > 1 Then
Set rng2 = rng1.Offset(1, 0).Resize(rng1.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible).Cells(1, 1)
MsgBox rng2.Address
End If
回答by rory.ap
You can just use ActiveCell.Rowand ActiveCell.Column.
你可以只使用ActiveCell.Row和ActiveCell.Column。
回答by Dan Wagner
This will loop through column A too:
这也将遍历 A 列：
Option Explicit
Sub LoopThroughVisibleColA()
Dim MySheet As Worksheet
Dim FilterRange As Range, ColumnARange As Range, _
Cell As Range
Dim LastRow As Long, LastCol As Long
'set worksheet for easy reference
Set MySheet = ThisWorkbook.Worksheets("Sheet1")
'identify the ranges for our filter and loop action
LastRow = MySheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastCol = MySheet.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Set FilterRange = Range(MySheet.Cells(1, 1), MySheet.Cells(LastRow, LastCol))
Set ColumnARange = Range(MySheet.Cells(1, 1), MySheet.Cells(LastRow, 1))
'apply filter: in this example, filter column A for values >4
FilterRange.AutoFilter Field:=1, Criteria1:=">4"
'loop through the visible cells in our column A range
For Each Cell In ColumnARange.SpecialCells(xlCellTypeVisible)
If Cell.Row <> 1 Then 'skip the header row
MsgBox ("We're in row " & Cell.Row & " and column " & Cell.Column)
End If
Next Cell
End Sub
回答by blackworx
You could use Application.Sendkeys "{DOWN}", Truethen read Selection.Address
你可以使用Application.Sendkeys "{DOWN}", True然后阅读Selection.Address
