选择过滤列标题正下方的第一个可见单元格 [英] Select first visible cell directly beneath the header of a filtered column
问题描述
我试图选择过滤列标题正下方的第一个可见单元格.我得到的代码如下,但是我必须对此代码有问题.首先,第一行代码正在使用文件的当前活动范围.此文件很有可能会更改,并且此范围将不同.如何使它适用于我将在其上使用的任何文件?其次,如果我使用完全相同的列格式的完全不同的文件,则列J下的第一个可见单元格可能是J210.如何使此方法适用于任何变量数组?
I am trying to select the first visible cell directly beneath the header of a filtered column. The code I am getting is as below, but I have to problems with this code. First, the first line of code is using the current active range of the file. It is highly likely that this file will change and this range will not be the same. How can I make it work for any file I would use it on? Second, if I use a totally different file with the same column format, the first visible cell under Column J could be J210. How can I make this work for any array of variables?
Sub Macro16()
'
' Macro16 Macro
'
'
ActiveSheet.Range("$A$1:$R$58418").AutoFilter Field:=12, Criteria1:= _
"Sheets"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[1],3)"
Selection.FillDown
End Sub
推荐答案
未经测试,但:
Sub Macro16()
With ActiveSheet.Range("A1").CurrentRegion
.AutoFilter field:=12, Criteria1:="Sheets"
If .Columns(1).SpecialCells(xlCellTypeVisible).count > 1 Then
With .Columns(10)
.Resize(.rows.count - 1).offset(1).SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=RIGHT(RC[1],3)"
End With
End If
End With
End Sub
这篇关于选择过滤列标题正下方的第一个可见单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!